cancel
Showing results for
Did you mean:

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

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?

Many thanks

Elizabeth

11 REPLIES 11
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!

Regular Visitor

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!

Community Champion

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

Regular Visitor

thank you so much, that worked!

Regular Visitor

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 🙂

Regular Visitor

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

Regular Visitor

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

Regular Visitor

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?

Community Champion

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!

Regular Visitor

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

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