An option in a report to switch a slicer date interval to one year ago

[Anonymous]

Hey PowerBi Community,

As a beginner it seems to be a simple question but I still could not find an answer to it anywhere on the internet.

I want to include an option in my report (what I recon could be some kind of DATE function, or a bookmark) that would let the viewer see the same interval of dates but a year ago. Is it even possible? I have a simple card visual with sales number on it (which is quantity and price multiplied for every invoice and summed up) and I want to display another card visual next to it which shows sales number a year ago. If the client selects Jan-Feb of 2020 then the card next to an original should show the number of sales in the interval Jan-Feb 2019 for example.

Thank you in advance,

Zenons

[amitchandak · Accepted solution]

@Anonymous , with a calendar date table marked as date.

example

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

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.

[Anonymous]

This one worked like magic! Previously I this formula:

Sale a year ago = CALCULATE(SUMX('SALES','SALES'[Quantity]*'SALES'[Sales Price]), DATEADD(ALLSELECTED(SALES[Date of shipment]),-1,YEAR))

But your 

Sale a year ago = CALCULATE(SUMX('SALES','SALES'[Quantity]*'SALES'[Sales Price]), DATEADD(SALES[Date of shipment],-1,YEAR))

worked perfectly.

I thought that you always have to place that ALLSELECTED formula so that it filters properly, but I guess not. I will look into ALLSELECTED formula more. Big thanks for such a quick and precise response! I would give you more than one kudos if I could 🙂

[fhill]

Quick Option / Thought:

Are you able to have the slicer select just the 'Months' (without year option) and FILTER Card one to just 2019 dates, and FILTER Card two to just 2020 Dates?  Or, do you have multiple years back of data we need to code for, so if December 2018 & January 2019 are selected, then the 2nd Card needs to show December 2017 & January 2018, etc...

FOrrest

[Anonymous]

I have a single table with three years of sales, so your filter will not work 😞 I also want to have an option to select and interval in between years, just as you said.