Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
ElizabethFoy
Regular Visitor

How to show "sameperiodlastyear" with a date slicer

I am new to PowerBI and learning as I go along.

 

I have a finance transaction table of GL transactions. I want to produce a table with actuals vs priorperiod last year YTD

 

I have created a prior year measure using the "sameperiodlastyear" DAX formula.

This works perfectly................ until I then add in a date slicer (from the Dates table).

 

I need a date slicer otherwise the actuals pull in every ledger item to date rather than say Jan-Jul19.  Unfortunately, once the data slicer is added to show actuals for 2019, the Prior Year column then goes blank. 

 

How do I overcome this?

 

Any advice would be appreciated

 

Many thanks

Elizabeth

11 REPLIES 11
tex628
Community Champion
Community Champion

You should be able to use something like this: 

Measure = 
VAR dateRange = 'Calendar'[Date]
return
Calculate(
[Amount],
All('Calendar'),
'Calendar'[Date] = Sameperiodlastyear(dateRange)
)


Let me know if it doesnt work! 


Connect on LinkedIn

Thank you. How do I input in that format? I feel daft for asking but I`m quite happy to admit it is all rather new to me.  I`m an advanced excel user...but having to start from scratch with my knowledge in PowerBI! 

 

Measure = 
VAR dateRange = DATESBETWEEN('Dates'[Dates], MIN('Dates'[Dates]),MAX('Dates'[Dates]))
Var dateRange2 = Sameperiodlastyear(dateRange)
return
Calculate(
[Sum of Transaction Value WAvg GBP],
ALL('Dates'),
dateRange2
)

Had to make some small changes but i hope it works, should be with the correct table and measure names!

 


Connect on LinkedIn

thank you so much, that worked!

OK. So I have noticed I now have a slightly different issue. So while it retains the prior year data when I use slicer... I can now see actuals for data outside of the period I want to look at... How do I hide this?

PY and date slicers.JPG

 

Thank you 🙂

My slicer is pulling dates from the dates table........ should I have been using the date from my ledger table?

If I use the date from my ledger table, I lose the PY value



 

I`ve just realised I described the issue incorrectly.  It is showing the 2018 column as well as the 2019 column (the one I am interested in). How do I remove it without losing the PY information shown in the 2019 columns?

I didn't know that you had the year as the column dimension, that changes things! 

Try this,

Measure = 
VAR dateRange = DATESBETWEEN('Dates'[Dates], MIN('Dates'[Dates]),MAX('Dates'[Dates]))
Var dateRange2 = Sameperiodlastyear(dateRange)
return
Calculate(
[Sum of Transaction Value WAvg GBP],
Allselected('Dates'),
dateRange2
)


It might work, im not 100% sure!

 


Connect on LinkedIn

Interestingly, the moment you mentioned dates as columns, I suddenly realised that if I just remove "dates" from the column the issue goes away! 

 

However, as there will be other occasions where I may wish to have comparisons across the years (ie 2018 actuals vs PU, 2018 actuals vs Py as in the table above), I still need to solve the issue.

 

I`ll give your code a go and revert 🙂

Thank you, again

ElizabethFoy
Regular Visitor

I forgot to add my formula for Prior Year:

 

Sum of Transaction Value WAvg GBP PY = CALCULATE([Sum of Transaction Value WAvg GBP], SAMEPERIODLASTYEAR(Dates[Dates].[Date]))

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.