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...
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.

  • Icey's avatar
    Icey
    5 years ago

    Hi adsam ,

     

    Please check the attached .pbix file.

     

    Best regards

    Icey

     

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

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.

  • adsam's avatar
    adsam
    Frequent Visitor
    5 years ago

    thanks Icey for the help, just some issues

    1. once releasing year changes then there is no value. 

    2. I break the measure into all the variables calculated in your measure but previous forecast is not calculating correctly. which i highlited with Orange color, file link is here.  

    3. I added 1 more column (FCV) combining releasing period and releasing Year so DAX will calculate variance in all period even when year changes, but i m unable to create measure based on FCV column e.g. MinPeriod, MaxPeriod, Previous forecast, current forecast, difference

     

    I will highly appreciate your help.  

    • Icey's avatar
      Icey
      Community Support
      5 years ago

      Hi adsam ,

       

      Try this:

      Measure = 
VAR MinPeriod =
    CALCULATE (
        MIN ( Data[Period_ Column] ),
        FILTER (
            ALLEXCEPT ( Data, Data[Release Year], Data[Releasing Period] ),
            Data[Forecast] > 0
        )
    )
VAR MaxPeriod =
    SWITCH (
        MAX ( Data[Releasing Period] ) = 1,
        TRUE (),
            CALCULATE (
                MAX ( Data[Period_ Column] ),
                FILTER (
                    ALL ( Data ),
                    Data[Forecast] > 0
                        && Data[Releasing Period] = 12
                        && Data[Release Year]
                            = MAX ( Data[Release Year] ) - 1
                )
            ),
        CALCULATE (
            MAX ( Data[Period_ Column] ),
            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_ Column] >= MinPeriod
                && Data[Period_ Column] <= MaxPeriod
        )
    )
VAR PreviousVersion =
    SWITCH (
        MAX ( Data[Releasing Period] ) = 1,
        TRUE (),
            CALCULATE (
                SUM ( Data[Forecast] ),
                FILTER (
                    ALL ( Data ),
                    Data[Release Year]
                        = MAX ( Data[Release Year] ) - 1
                        && Data[Releasing Period] = 12
                        && Data[Period_ Column] >= MinPeriod
                        && Data[Period_ Column] <= MaxPeriod
                )
            ),
        CALCULATE (
            SUM ( Data[Forecast] ),
            FILTER (
                ALLEXCEPT ( Data, Data[Release Year] ),
                Data[Releasing Period]
                    = MAX ( Data[Releasing Period] ) - 1
                    && Data[Period_ Column] >= MinPeriod
                    && Data[Period_ Column] <= MaxPeriod
            )
        )
    )
RETURN
    IF (
        HASONEFILTER ( Periods[Date].[Month] ),
        SUM( Data[Forecast] ),
        CurrentVersion - PreviousVersion
    )

       

      Or this:

      Measure 2 = 
VAR MinPeriod =
    CALCULATE (
        MIN ( Data[Period_ Column] ),
        FILTER (
            ALLEXCEPT ( Data, Data[Release Year], Data[Releasing Period] ),
            Data[Forecast] > 0
        )
    )
VAR MaxPeriod =
    SWITCH (
        MAX ( Data[Releasing Period] ) = 1,
        TRUE (),
            CALCULATE (
                MAX ( Data[Period_ Column] ),
                FILTER (
                    ALL ( Data ),
                    Data[Forecast] > 0
                        && Data[Releasing Period] = 12
                        && Data[Release Year]
                            = MAX ( Data[Release Year] ) - 1
                )
            ),
        CALCULATE (
            MAX ( Data[Period_ Column] ),
            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_ Column] >= MinPeriod
                && Data[Period_ Column] <= MaxPeriod
        )
    )
VAR PreviousVersion =
    SWITCH (
        MAX ( Data[Releasing Period] ) = 1,
        TRUE (),
            CALCULATE (
                SUM ( Data[Forecast] ),
                FILTER (
                    ALL ( Data ),
                    Data[Release Year]
                        = MAX ( Data[Release Year] ) - 1
                        && Data[Releasing Period] = 12
                        && Data[Period_ Column] >= MinPeriod
                        && Data[Period_ Column] <= MaxPeriod
                )
            ),
        CALCULATE (
            SUM ( Data[Forecast] ),
            FILTER (
                ALLEXCEPT ( Data, Data[Release Year] ),
                Data[Releasing Period]=MAX(Data[Releasing Period])-1
                    && Data[Period_ Column] >= MinPeriod
                    && Data[Period_ Column] <= MaxPeriod
            )
        )
    )
RETURN
    IF (
        ISFILTERED(Data[Period_ Column]),
        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.

      • adsam's avatar
        adsam
        Frequent Visitor
        5 years ago

        thanks Icey 

        i have joined "Releasing period" & "Releasing year" columns in Column "FCV", so i want to calculate Dax based on that 1 column only, also i want to calculate all variables in independent measures, e.g. "MinPeriod", "MaxPeriod", "PreviousVersion", "CurrentVersion" & "Vance.  file link is here.  

         

        I will be very obliged.