cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

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 so there are months when I don't have data.

Raw material value table

Columns: Year/month

Rows: Prices of different raw materials

flitvak_0-1616788291671.png

The table shows that for example during October and November 2020 there was no price update.

For cases where there are ups in consecutive months, I had no problem getting the %

flitvak_1-1616788674168.png

In other words, in this case I would be short to obtain the percentage change between September 2020 and December 2020 (among several other periods).

From now on I appreciate any contribution!!!

Best regards

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

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 )

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
lbendlin
Super User
Super User

Have a separate table with all the months (preferably a proper calendar table)  and then create a measure that calculates for each month what the latest price update is (either for the current month or for the max month that is smaller than the current month).

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!