Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Power BI : Moving Average with Non Continuous Dates

Hi All,

 

I need to calculate moving average for past 5 days in DAX but my dates are not continuous (no data on weekends + public holidays).

My data looks like below.

After some research, I was able to write below measure, but it works fine only for continuous dates.

e.g : When the Report Date is selected as 11-Mar, it calculates moving avg as (10+15+20+25+30)/5 = 20.

 

However, when the Report Date is set to 09-Mar, it calculates as (20+25+30)/3 = 25 since data for 6th and 5th Mar is not found.

Ideally it should be (20+25+30+35+40)/5 = 30

 

5 day moving average =
Calculate

        AVERAGEX(Transactional Table, Transactional Table [Value]),
        DATESINPERIOD(
                Data table [Report Date],
                [Report Date], // this is the measure which gives selected report date

                -5,

                 Day))

 

Can someone please help me modify above measure to work with non-continuous dates. amitchandak GilbertQ 

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , Create a rank on the date of table ( Best is separate table with distinct dates)

     

    Rank = rankx('Transactional Table','Transactional Table'[Report Date],,asc, dense)

     

    CALCULATE(Average(Transactional Table [Value]) , FILTER(ALL('Transactional Table'), 'Transactional Table'[Rank] <= max('Transactional Table'[ Rank]) && 'Transactional Table'[Rank] >= max('Transactional Table'[ Rank])-4 ))

     

    or

     

    CALCULATE(Average(Transactional Table [Value]) , FILTER(ALL('Transactional Table'), 'Transactional Table'[Rank] <= max('Transactional Table'[ Rank]) && 'Transactional Table'[Rank] >= max('Transactional Table'[ Rank])-5 ))

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Create a rank on the date of table ( Best is separate table with distinct dates)

     

    Rank = rankx('Transactional Table','Transactional Table'[Report Date],,asc, dense)

     

    CALCULATE(Average(Transactional Table [Value]) , FILTER(ALL('Transactional Table'), 'Transactional Table'[Rank] <= max('Transactional Table'[ Rank]) && 'Transactional Table'[Rank] >= max('Transactional Table'[ Rank])-4 ))

     

    or

     

    CALCULATE(Average(Transactional Table [Value]) , FILTER(ALL('Transactional Table'), 'Transactional Table'[Rank] <= max('Transactional Table'[ Rank]) && 'Transactional Table'[Rank] >= max('Transactional Table'[ Rank])-5 ))

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you amitchandak for the quick response, but if I create a new column for rank then will it be dynamic? Becasue there is a slicer for Report Date Selection, based on that past 5 days keep changing

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        4 years ago

        Anonymous , Yes I think so. Max of Rank in formula should ensure that

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    You can try this expression:

    5 day moving average 1 = 
VAR _f =
    TOPN (
        5,
        FILTER (
            ALL ( Sheet2[Report Date] ),
            [Report Date] <= SELECTEDVALUE ( Sheet2[Report Date] )
        ),
        [Report Date]
    )
RETURN
    CALCULATE ( SUM ( 'Transactional Table'[Value] ), _f ) / 5

    Result:

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

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