Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
5 years ago
Solved

Variation from previous period (discontinuous periods)

Good afternoon, the company where I work I need to make a report with the percentage variation in raw materials over the last three years. The downside is that raw materials don't go up every month ...
  • Anonymous's avatar
    Anonymous
    5 years ago

    HI flitvak,

    I think you may need to use Dax function to find out the last date that existed valid value in your table, then you can use this with the current category value to lookup corresponding value to compare with current and previous values.

     

    measure =
VAR currDate =
    MAX ( Table[Date] )
VAR prevDate =
    CALCULATE (
        MAX ( Table[Date] ),
        FILTER ( ALLSELECTED ( Table ), [Date] < currDate && [Amount] <> BLANK () ),
        VALUES ( Table[Category] )
    )
VAR prevAmount =
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER ( ALLSELECTED ( Table ), [Date] = prevDate ),
        VALUES ( Table[Category] )
    )
RETURN
    DIVIDE ( SUM ( Table[Amount] ) - prevAmount, prevAmount )

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
5 years ago

HI flitvak,

I think you may need to use Dax function to find out the last date that existed valid value in your table, then you can use this with the current category value to lookup corresponding value to compare with current and previous values.

 

measure =
VAR currDate =
    MAX ( Table[Date] )
VAR prevDate =
    CALCULATE (
        MAX ( Table[Date] ),
        FILTER ( ALLSELECTED ( Table ), [Date] < currDate && [Amount] <> BLANK () ),
        VALUES ( Table[Category] )
    )
VAR prevAmount =
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER ( ALLSELECTED ( Table ), [Date] = prevDate ),
        VALUES ( Table[Category] )
    )
RETURN
    DIVIDE ( SUM ( Table[Amount] ) - prevAmount, prevAmount )

 

Regards,

Xiaoxin Sheng

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    5 years ago

    Thank you very much Xiaoxin for the help!!! It was just what I needed!!!

    As a whole I adapted the date in the currDate variable and it worked perfectly. The code looked like this to me:

    % Increase ?
    VAR currDate =
    MAX ( OCA[Date of Issue] )
    VAR prevDate =
    CALCULATE (
    MAX ( 'Calendar Table'[Date] );
    FILTER ( ALLSELECTED ('Calendar Table'); 'Calendar table'[Date] < currDate && [MAX price] <> BLANK() );
    VALUES ( OCA[Article] )
    )
    VAR prevAmount =
    CALCULATE (
    max(OCA[Unit price without discounts UM purchases]);
    FILTER ( ALLSELECTED ( 'Calendar Table' ); 'Calendar table'[Date] ? prevDate );
    VALUES ( OCA[Article] )
    )
    RETURN
    DIVIDE ( max(OCA[Unit price without discounts UM purchases]) - prevAmount; prevAmount )

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi flitvak,

      I'm glad to hear my formula helps. 😊

      If you have any other requirements, you can feel free to post here with detailed information.
      Regards,

      Xiaoxin Sheng