Date Slicer - Previous Year

[adam_b]

Hi,
First post here, looking to see if someone can point me in the right direction!

I have a Matrix visualization, I have a date slicer on the same table which I select the date range and it filters the Matrix.

I am looking to see if its possible to add a measure to the table / matrix which would display the previous year of whatever date range has been selected in the Date Slicer. 

E.g. if the date slicer has 01/05/20 to 31/08/20 then the Measure should caluclate field Cost based on 01/05/19 to 31/08/19?

Thanks,

[amitchandak · Accepted solution]

@adam_b , with a date calendar use year behind measure

example

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

or sameperiodlast year

SAMEPERIODLASTYEAR Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[v-deddai1-msft · Accepted solution]

Hi @adam_b ,

First you need to create a calendar table(The time intelligence function generally requires the use of a calendar table

), please follow the link https://softcrylic.com/blogs/power-bi-for-beginners-how-to-create-a-date-table-in-power-bi/. Then create one to many relationship between the date table and fact table.

Then use date column in calendar as slicer and create the following measure:

Last year cost = CALCULATE(SUM('Table'[Cost]),dateadd('Calendar'[Date],-1,Year))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Greg_Deckler]

@adam_b - I use a Complex Selector for this sort of thing. https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

[amitchandak · Accepted solution]

@adam_b , with a date calendar use year behind measure

example

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

or sameperiodlast year

SAMEPERIODLASTYEAR Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.