Running Totals

[Brendy_P]

Hi 

I have the solution but want to understand the logic of the solution, below is the formula with the logic of what I think the formula is performing. Can someone correct me in understanding the logic or am I correct.

Running Total (Filter) =

CALCULATE([Sales],

FILTER(ALL(Full_Date_Table),-- We are going to filter the full dates table but before we do make sure all the filters are cleared

Full_Date_Table[Date]>MAX(Full_Date_Table[Date])-30&&-- If the date in the filter context is > to the date(s) of the Max date in the filter context then go back 30 days (MAX is a scalar Function) AND

Full_Date_Table[Date]<=MAX(Full_Date_Table[Date]))) -- If the date in the filter context is <= to Max date in the filter context then Calculate sales

I know I can create the same formula with 

Running Totals (DatesInPeriod) =

CALCULATE([Sales],

DATESINPERIOD(Full_Date_Table[Date],

MAX(Full_Date_Table[Date]),-30,DAY))

But I want to get my head around how the first formula works. 

Thanks for your time

[v-easonf-msft]

Hi, @Brendy_P 

Yes, no problem at all. DATESINPERIOD function and the filter conditions both return the same date range.

Although, each function has its own usage, you can tweak and change the expression to get the same result as another function.

Best Regards,
Community Support Team _ Eason

[Greg_Deckler · Accepted solution]

@Brendy_P Seems like you understand things correctly. Looks like you are calculating a measure called Sales. You first break out of filter context by using ALL. Then you impose your own filter context where you are retrieving the last 30 days. So the maximum date in context and then back 30 days from there. 

I personally avoid time "intelligence" functions if possible. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see my Time Intelligence the Hard Way:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...