Forum Discussion
Helper IIIMoving average last 90 days without using Date filter
Hi friends,
I created the moving average last 90 days measure:
Average L90D =
CALCULATE([Quantity],
Filter (all (D_Date),
D_Date[Date] > max (D_Date[Date])-90 &&
D_Date[Date] <= MAX(D_Date[Date]))
)/90
It only works if I chose last 90 days.
If I delete this filter, It will give blank values.
I tried another way with this code:
Average last90D V2=
CALCULATE(
DIVIDE(
CALCULATE (
[Quantity],
DATESINPERIOD ( F_Sale[Date], MAX ( F_Sale[Date] ), -90, DAY )
),90))
This code is working without data filter, however when I export the data, the grand total does not match to the total on Power BI.
I would like to have a measure calculate Moving average last 90 days without using Date filter and the grand total exported to excel should match to the total on Power BI.
Highly appreciate your help.
Thank you in advance.
I found a trick to solve this problem: just set a relative 90 days date for date filter on filter panel.
3 Replies
- amitchandak
Super User
tracytran91 , Max of D_Date will take max date as date of the calendar if nothing selected same if true for another formula too. But in that, you are using max F_Sale's date. So max is the last available data.
In case of second the grand total will last 90 days of data.
tracytran91Average last90D V2= CALCULATE( DIVIDE( CALCULATE ( [Quantity], DATESINPERIOD ( F_Sale[Date], MAX ( F_Sale[Date] ), -90, DAY ) ),90))With this measure, I export data to excel, the grand total does not match to the grand total on Power BI. That's problem
- tracytran91
I found a trick to solve this problem: just set a relative 90 days date for date filter on filter panel.
