Sameperiodlastyear - exclude future dates | Forum

Resolved · High Priority · Version 365

Mon 25 Jan 2021, 16:58 Delegate 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:49 Trainer 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:34 Delegate 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:36 Trainer 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

Training courses

	98.7 % recommend us
	More live stats...

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

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.

View all Power BI hints and tips