Forum Discussion

StefanoP's avatar
StefanoP
Regular Visitor
3 years ago

Table with computed values that populate a measure table based on 2 date filters

Hello there, I'm a fresh approacher to this world of PowerBi and its related products and functions. Even if I understand ( more or less ) how it works and what to do, I'm stuck in "how to do it". ...
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi StefanoP 

this is a simple 3 months moving average. There is no need to create a measure for each month. I mean this us DAX, you can play with the filter context the way you want. 
a snapshot of your dimCalendar table will be helpful to find a solution suitable to this case

StefanoP's avatar
StefanoP
Regular Visitor
3 years ago

Here is the screen of the issue table

 

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    StefanoP 

    I meant the DLCalendar(period)

    • StefanoP's avatar
      StefanoP
      Regular Visitor
      3 years ago

      Honestly, period in DLCalendar can be taken out of computation, since all periods that matters are in CalendarVolumes, but here we are :

       

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        StefanoP 
        First add the following calculated column in the DLCalendar table

        Period Rank =
RANKX ( DLCalendar, DLCalendar[Period],, ASC, DENSE )

        Then use the following measure

        3 Months Moving Average =
VAR CurrentPeriodRank =
    MAX ( DLCalendar[Period Rank] )
RETURN
    CALCULATE (
        SUM ( ActualTable[Value] ),
        DLCalendar[Period Rank] <= CurrentPeriodRank,
        DLCalendar[Period Rank] > CurrentPeriodRank - 3,
        ALL ( DLCalendar )
    ) / 3