Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
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!
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!
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?
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!
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
I forgot to add my formula for Prior Year:
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
82 | |
70 | |
49 |
User | Count |
---|---|
143 | |
125 | |
108 | |
60 | |
55 |