How to show "sameperiodlastyear" with a date slicer

[ElizabethFoy]

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

[tex628]

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! 

[ElizabethFoy]

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! 

[tex628]

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!

[ElizabethFoy]

thank you so much, that worked!

[ElizabethFoy]

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 🙂

[ElizabethFoy]

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

[ElizabethFoy]

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

---

---

[ElizabethFoy]

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?

[tex628]

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!

[ElizabethFoy]

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]

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