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
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
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
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
