Relative date slicer to calculate % change for same period last year

[ksrini]

Hi All,

I have a relative date slicer where user can select dates by dragging the bar left to right. I have one KPI card that calculates % change based on start and end dates selected by user. I want to build another card which % change for same period last year based on the date selected by user. In this below example the 2nd card should calculate % change over 12/29/2018 - 7/1/2019 since it is same period last year. I tried datesbetween formulae but it calculates for current period but not for previous year. YTD is close to it but it doesn't change based on user selection. Any ideas ?

Thanks,

Srinivas

[Icey · Accepted solution]

Hi @ksrini ,

I create a simple example. Please check whether the attached file is helpful.

Change % = 
VAR StartDate =
    MIN ( Dates[Date] )
VAR EndDate =
    MAX ( Dates[Date] )
VAR StartValue =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Date] = StartDate )
VAR EndValue =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Date] = EndDate )
RETURN
    DIVIDE ( EndValue - StartValue, StartValue, 0 )

Change % last year = CALCULATE([Change %],SAMEPERIODLASTYEAR(Dates[Date]))

Best Regards,

Icey

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

[ksrini]

Hi Icey,

Thanks for the solution and for explaining it in such simple example.

Best regards,

Srinivas 

[amitchandak]

@ksrini , Create a use a date table. Use date Table for slicer too.

And create a measure like

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

The take diff with SUM(Sales[Sales Amount])

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

Appreciate your Kudos.

if this does not work.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.