Calculate X Period for each date row

[Anonymous]

hey all, whats up?

so my boss told me he wants a line chart that presents the % difference between two running totals.. so far so good - i have created the measures for the running total which:

Running Total This Year = CALCULATE([TotalRevenueThisYearLC],FILTER(ALLSELECTED('Data_model_View (2)'),'Data_model_View (2)'[DY_Date]<=MAX('Data_model_View (2)'[DY_Date])))

Running Total Last Year = CALCULATE([TotalRevenueLastYearLC],FILTER(ALLSELECTED('Data_model_View (2)'),'Data_model_View (2)'[DY_Date]<=MAX('Data_model_View (2)'[DY_Date])))

and i have created the % difference

% of Running Total This Year & Last Year =

VAR BaseValue = [Running Total Last Year]

VAR CompareValue = [Running Total This Year]

RETURN

    IF(

        NOT ISBLANK(CompareValue),

        DIVIDE(CompareValue - BaseValue, BaseValue)

    )

everything works great, but now he told me that he wants to every date row (28/05/2020 for example) calculate the total for the last 4 weeks. 

like if its 28/05/2020 then i need to somehow calculate the past 4 weeks, and also for 27/05/2020, 26/05/2020, 25/05/2020... etc 

how can i do it? can i do it at all? 

thanks

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

We can use the following measure to meet your requirement.

Last 4 weeks = 
var last_4 = MAX('Table'[Date])-4*7
var current_ = MAX('Table'[Date])
return
CALCULATE(SUM('Table'[values]),FILTER(ALLSELECTED('Table'),'Table'[Date]>last_4 && 'Table'[Date] <= current_))

The Last 4 weeks values doesn’t change if you select a slicer date, you can use the following measure. Replace ALLSELECTED with ALL.

Last 4 weeks = 
var last_4 = MAX('Table'[Date])-4*7
var current_ = MAX('Table'[Date])
return
CALCULATE(SUM('Table'[values]),FILTER(ALL('Table'),'Table'[Date]>last_4 && 'Table'[Date] <= current_))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

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

[Anonymous]

hey @v-zhenbw-msft , thanks alot for your message, its working! the 2nd measure is exactly what i needed. 

THANKS!!