Forum Discussion

adsam's avatar
adsam
Frequent Visitor
5 years ago
Solved

Forecast version comparison measure

Hi, every month we are releasing forecast for forward period by lapsing 1 period and increasing some period in foreward months, I want to create a comparison measure which compare the current version forecast with the previous version forecast in overlaping period. i can link those periods and version with the 1st date of period & forecast version with releasing date. example showen in below picture, looking forward for the help. 

 

Need Help
  • Icey's avatar
    Icey
    5 years ago

    Hi adsam ,

     

    Please check if this could meet your requirements:

    Period_ Column = 
RIGHT ( [Month], 4 ) & LEFT ( RIGHT ( [Month], 9 ), 2 )

    Measure =
VAR MinPeriod =
    CALCULATE (
        MIN ( Data[Period_] ),
        FILTER (
            ALLEXCEPT ( Data, Data[Release Year], Data[Releasing Period] ),
            Data[Forecast] > 0
        )
    )
VAR MaxPeriod =
    CALCULATE (
        MAX ( Data[Period_] ),
        FILTER (
            ALLEXCEPT ( Data, Data[Release Year] ),
            Data[Forecast] > 0
                && Data[Releasing Period]
                    = MAX ( Data[Releasing Period] ) - 1
        )
    )
VAR CurrentVersion =
    CALCULATE (
        SUM ( Data[Forecast] ),
        FILTER (
            ALLEXCEPT ( Data, Data[Release Year], Data[Releasing Period] ),
            Data[Period_] >= MinPeriod
                && Data[Period_] <= MaxPeriod
        )
    )
VAR PreviousVersion =
    CALCULATE (
        SUM ( Data[Forecast] ),
        FILTER (
            ALLEXCEPT ( Data, Data[Release Year] ),
            Data[Releasing Period]
                = MAX ( Data[Releasing Period] ) - 1
                && Data[Period_] >= MinPeriod
                && Data[Period_] <= MaxPeriod
        )
    )
RETURN
    IF (
        HASONEFILTER ( Periods[Date].[Month] ),
        SUM ( Data[Forecast] ),
        CurrentVersion - PreviousVersion
    )

     

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.

8 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    Please provide sample data in usable format (not as a picture) and show the expected outcome.

    • adsam's avatar
      adsam
      Frequent Visitor
      5 years ago

      Link to file is here, expected outcome is below to the table, means i want to compare current version of forecast with the previous version of forecast with the overlaping periods. 

      • Icey's avatar
        Icey
        Community Support
        5 years ago

        Hi adsam ,

         

        Please check if this could meet your requirements:

        Period_ Column = 
RIGHT ( [Month], 4 ) & LEFT ( RIGHT ( [Month], 9 ), 2 )

        Measure =
VAR MinPeriod =
    CALCULATE (
        MIN ( Data[Period_] ),
        FILTER (
            ALLEXCEPT ( Data, Data[Release Year], Data[Releasing Period] ),
            Data[Forecast] > 0
        )
    )
VAR MaxPeriod =
    CALCULATE (
        MAX ( Data[Period_] ),
        FILTER (
            ALLEXCEPT ( Data, Data[Release Year] ),
            Data[Forecast] > 0
                && Data[Releasing Period]
                    = MAX ( Data[Releasing Period] ) - 1
        )
    )
VAR CurrentVersion =
    CALCULATE (
        SUM ( Data[Forecast] ),
        FILTER (
            ALLEXCEPT ( Data, Data[Release Year], Data[Releasing Period] ),
            Data[Period_] >= MinPeriod
                && Data[Period_] <= MaxPeriod
        )
    )
VAR PreviousVersion =
    CALCULATE (
        SUM ( Data[Forecast] ),
        FILTER (
            ALLEXCEPT ( Data, Data[Release Year] ),
            Data[Releasing Period]
                = MAX ( Data[Releasing Period] ) - 1
                && Data[Period_] >= MinPeriod
                && Data[Period_] <= MaxPeriod
        )
    )
RETURN
    IF (
        HASONEFILTER ( Periods[Date].[Month] ),
        SUM ( Data[Forecast] ),
        CurrentVersion - PreviousVersion
    )

         

         

        Best regards

        Icey

         

        If this post helps, then consider Accepting it as the solution to help other members find it faster.