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". ...
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
      • StefanoP's avatar
        StefanoP
        Regular Visitor
        3 years ago

        Thank you very much, now I understand better.

        Tell me if I get it right :

        With period rank you obtain an added column to index periods.

        With 3 months moving average, CurrentPeriod is a var set to max period based on a slicer, then return a table where the value is find and added on itself by using periodrank correlation in a fork between a max ( period rank ) and max-3 period.

        Thank you for you support.