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 : 

DateProduct Price Rank
22/08/2017A95.501
22/08/2017B61.111
22/08/2017C65.741
21/08/2017A49.612
21/08/2017B60.612
21/08/2017C52.302
20/08/2017A75.133
20/08/2017B91.193
20/08/2017C85.003
16/08/2017A37.844
16/08/2017B99.514
16/08/2017C94.634

 

I 'm trying to compute the Day on Day price variation for each product. I managed to have something that works only if i have data on each days (but i will not have always daily datas, most of the time, nothing on weekend and sometimes one data points can miss on week days also)

Here is my column formula :

 

DoD Price= CALCULATE(
     ((AVERAGE(DataBase[Price]))-CALCULATE(
         AVERAGE(DataBase[Price]);DATEADD(DataBase[Date];-1;DAY);
         ALLEXCEPT(DataBase;DataBase[Date];DataBase[Product])));
     ALLEXCEPT(DataBase;DataBase[Date];DataBase[Product])
)

I was thinking about using the "Rank" column wich  is computed with this formula : 

Rank = RANKX(DataBase;DataBase[Date];;DESC;Dense)

 

 

But i don't know if and how i can use to compute somthing like 

 

CALCULATE (
                        AVERAGE(DataBase[Price])  - 
                        CALCULATE( AVERAGE(DataBase[Price]);RANK=RANK+1))

since the Rank+1 will always existe except for the oldest value. 

 

 

 

Many thanks for your help

Regards

Pierre

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 ! 

     

7 Replies

  • 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