cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Calculate X Period for each date row

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
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

## Re: Calculate X Period for each date row

Hi @CSDEV ,

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.

2 REPLIES 2
Highlighted
Community Support

## Re: Calculate X Period for each date row

Hi @CSDEV ,

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.

Highlighted
Frequent Visitor

## Re: Calculate X Period for each date row

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

THANKS!!

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors