Forum Discussion

pitucc's avatar
pitucc
Helper I
9 years ago
Solved

DoD Variation column using Dateaddd on Business Days

Hi,  Here is the kind of data set i have :  Date Product  Price  Rank 22/08/2017 A 95.50 1 22/08/2017 B 61.11 1 22/08/2017 C 65.74 1 21/08/2017 A 49.61 2 21/08/2017...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi pitucc,

     

    My formula is a measure, if you direct use it as calculate column ,it may caused the issue.

    In addition, if you need you can also use below formula: (calculate column version)

     

    DoD diff =
VAR previous_date =
    MAXX ( FILTER ( ALL ( 'Sample' ), [Date] < EARLIER ( [Date] ) ), [Date] )
RETURN
    IF (
        previous_date <> 0,
        AVERAGEX (
            FILTER (
                ALL ( 'Sample' ),
                [Product] = EARLIER ( [Product] )
                    && [Date] = EARLIER ( [Date] )
            ),
            [Price]
        )
            - AVERAGEX (
                FILTER (
                    ALL ( 'Sample' ),
                    [Product] = EARLIER ( [Product] )
                        && [Date] = previous_date
                ),
                [Price]
            ),
        0
    )

     

     

    Regards,

    Xiaoxin Sheng

  • pitucc's avatar
    pitucc
    9 years ago

    Hello,

    I just wnated to close the topic, to compute a variation for anything alse than day on day, in the computation of "previous_avg" just replace "current_date" by " (current_date-XX) where XX is the lag you want for your variation. 

    For a WoW variation I have used the code below : 

    WoW Cheap = 
VAR current_Product =LASTNONBLANK ( DataBase[Name]; [Name] )
VAR current_date = MAX ( DataBase[ValuationDate] )
VAR current_avg =
    AVERAGEX (
        FILTER (
            ALL ( DataBase );
            [Name] = current_Product
                && [ValuationDate] = current_date
        );
        [Cheap]
    )
VAR previous_avg =
    AVERAGEX (
        FILTER (
            ALL ( DataBase );
            [Name] = current_Product
                && [ValuationDate]
                    = MAXX (
                        FILTER (
                            ALL ( DataBase );
                            [Name] = current_Product
                                && [ValuationDate] < (current_date-6)
                        );
                        [ValuationDate]
                    )
        );
        [Cheap]
    )
RETURN
    IF ( previous_avg <> 0; current_avg - previous_avg; 0 )

    Many thanks once again for your help ! 

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

So, in the case of a missing date, do you want to calculate from the last date that there is?

 

For example:

 

22/08/2017, A, 95.50

24/08/2017, A, 95.60

 

So, on 23/08/2017 the price difference should be 0 and on 24/08/2017 the price difference should be .10 (not 95.60)?

  • pitucc's avatar
    pitucc
    Helper I
    9 years ago

    exactly yes !  That's why i was thinking about using the "rank" column. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi pitucc,

       

      If you want to calculate day of day moving average diff, you can try to use below formula:

      DoD Avg Diff = 
VAR current_Product =LASTNONBLANK ( 'Sample'[Product], [Product] )
VAR current_date = MAX ( 'Sample'[Date] )
VAR current_avg =
    AVERAGEX (
        FILTER (
            ALL ( 'Sample' ),
            [Product] = current_Product
                && [Date] = current_date
        ),
        [Price]
    )
VAR previous_avg =
    AVERAGEX (
        FILTER (
            ALL ( 'Sample' ),
            [Product] = current_Product
                && [Date]
                    = MAXX (
                        FILTER (
                            ALL ( 'Sample' ),
                            [Product] = current_Product
                                && [Date] < current_date
                        ),
                        [Date]
                    )
        ),
        [Price]
    )
RETURN
    IF ( previous_avg <> 0, current_avg - previous_avg, 0 )

       

      Regards,

      Xiaoxin Sheng

      • pitucc's avatar
        pitucc
        Helper I
        9 years ago

        Hello,

        Thanks for your help but I have litterally copy pasted your formula but it don't have the same result as you do... 

         

         

        Any idea why ? 

        Furthermore, is the formula easely adaptable to compte a Month on month variation difference ? 

         

        Thanks