Forum Discussion

tracytran91's avatar
tracytran91
Icon for Helper III rankHelper III
5 years ago
Solved

Moving 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_D...
Need Help
  • tracytran91's avatar
    tracytran91
    5 years ago

    I found a trick to solve this problem: just set a relative 90 days date for date filter on filter panel. 

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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.

tracytran91's avatar
tracytran91
Icon for Helper III rankHelper III
5 years ago

amitchandak 

Average 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