98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Power BI training and help » Sameperiodlastyear - exclude future dates | Forum
Sameperiodlastyear - exclude future dates | Forum
Resolved · High Priority · Version 365
Mon 25 Jan 2021, 16:58Delegate Laurence said...
Laurence has attended:
Excel PowerPivot course
Sameperiodlastyear - exclude future dates
Hi,
I've got a table set up with years across the top and months down the side in Power BI.
I've got a measure for bookings
Bookings = DISTINCTCOUNT(BOOKINGS[BookId]))
& then a measure for last years bookings
Bookings LY (Bk date) = CALCULATE(BOOKINGS[Bookings],SAMEPERIODLASTYEAR('DATESBKGS&ENQS'[Dates-Bkg&Enq]))
& then simply one minute the other
Bookings V LY (Bk date) = BOOKINGS[Bookings]-BOOKINGS[Bookings LY (Bk date)]
So what I'm wanting to show on the table is the change year on year month, however what's happening that I'm trying to remove is, its comparing Feb 2021 (which is 0 as we're as of today still in Jan 21) against Feb 2020 but I want to remove that and only compare up until today, any ideas on how to achieve this? I've searched and searched but nothing seems to work......
Thanks
Mon 25 Jan 2021, 18:49Trainer Jens said...
RE: Sameperiodlastyear - exclude future dates
Hi Laurence,
Thank you for the forum question.
Try below measure. It replaces the two measure below
Bookings LY (Bk date) = CALCULATE(BOOKINGS[Bookings],SAMEPERIODLASTYEAR('DATESBKGS&ENQS'[Dates-Bkg&Enq]))
Bookings V LY (Bk date) = BOOKINGS[Bookings]-BOOKINGS[Bookings LY (Bk date)]
Replace the two above with the measure below
Bookings V LY (Bk date) =
Var BookingsLY(Bk date)=CALCULATE(BOOKINGS[Bookings],Sameperiodlastyear('DATESBKGS&ENQS'[Dates-Bkg&Enq]))
Return
if([Bookings]&&BookingsLY(Bk date),BOOKINGS[Bookings]-BOOKINGS[BookingsLY(Bk date)])
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 26 Jan 2021, 10:34Delegate Laurence said...
RE: Sameperiodlastyear - exclude future dates
Hi Jens,
Thank you for this answer, I actually stumbled upon a mother way of doing it by accident, but your formula looks useable too. thank you
Laurence
Tue 26 Jan 2021, 10:36Trainer Jens said...
RE: Sameperiodlastyear - exclude future dates
Well done Laurence,
I am happy that you found a solution.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Power BI tip:Master DAX (Data Analysis Expressions)DAX is the formula language used in Power BI for creating custom calculations and aggregations. Invest time in learning DAX to unlock the full potential of Power BI. Understand functions like CALCULATE, FILTER, and SUMX, as they are fundamental for creating complex calculations and measures. |
