Forum Discussion

powerjey's avatar
powerjey
Helper I
4 years ago
Solved

How to dynamically calculate the cost difference between two dates per item?

Hi everyone,

 

Looking for some help to calculate the cost increase/decrease between two date points per product. I would like to display any increases/decreases per item. Each item in the list are part of a BOM for an overall product. An example scenaro is needing to show any cost differences between 2022-03-01 & 2022-04-01 which would show changes in A ($0.50) and C ($0.20).

 

I'm guessing I would need to utilize the MAX/MIN functions in a measure, but having trouble creating a measure that compares each item. There are too many date points to due the calculation per date via a new column and all the data is unpivoted. 

 

  • 2 date values are selected via the standard PBI slicer visual
  • The underlying data format in PBI is displayed below:  
Part#Date Price 
A2022-01-01 $                                          1.25
A2022-02-01 $                                          1.25
A2022-03-01 $                                          1.25
A2022-04-01 $                                          1.75
A2022-05-01 $                                          1.75
B2022-01-01 $                                          3.50
B2022-02-01 $                                          3.50
B2022-03-01 $                                          3.50
B2022-04-01 $                                          3.50
B2022-05-01 $                                          3.50
C2022-01-01 $                                          2.00
C2022-02-01 $                                          2.00
C2022-03-01 $                                          2.00
C2022-04-01 $                                          2.20
C2022-05-01 $                                          2.20
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  powerjey ,

    Here are the steps you can follow：

    1. Create measure.

    Measure =
var _min=MINX(ALLSELECTED('Table'),[Date])
var _max=MAXX(ALLSELECTED('Table'),[Date])
return
SUMX(FILTER(ALL('Table'),'Table'[Date]=_max&&'Table'[Part#]=MAX('Table'[Part#])),[Price])
-
SUMX(FILTER(ALL('Table'),'Table'[Date]=_min&&'Table'[Part#]=MAX('Table'[Part#])),[Price])

    2. Result:

    If you need pbix, please click here.

    How to dynamically calculate the cost difference between two dates per item.pbix

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  powerjey ,

    Here are the steps you can follow：

    1. Create measure.

    Measure =
var _min=MINX(ALLSELECTED('Table'),[Date])
var _max=MAXX(ALLSELECTED('Table'),[Date])
return
SUMX(FILTER(ALL('Table'),'Table'[Date]=_max&&'Table'[Part#]=MAX('Table'[Part#])),[Price])
-
SUMX(FILTER(ALL('Table'),'Table'[Date]=_min&&'Table'[Part#]=MAX('Table'[Part#])),[Price])

    2. Result:

    If you need pbix, please click here.

    How to dynamically calculate the cost difference between two dates per item.pbix

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • powerjey's avatar
      powerjey
      Helper I
      4 years ago

      Thanks so much! Works perfectly. 

      I've learned alot from this post. 

      • lou_powerbi's avatar
        lou_powerbi
        Regular Visitor
        3 years ago

        In my case I have used your guidance to fix my issue.. Thanks a lot

         

        % new perc =
        var w_previous = MINX(ALLSELECTED('DP Fcast (2)'),[Week_final])
        var w_current = MAXX(ALLSELECTED('DP Fcast (2)'),[Week_final])

        var diff1 = SUMX(FILTER(ALL('DP Fcast (2)'),'DP Fcast (2)'[Week_final]= w_current&&'DP Fcast (2)'[Location]=MAX('DP Fcast (2)'[Location])),[Fcast Volume(in EA) by Location])
        var diff2 = SUMX(FILTER(ALL('DP Fcast (2)'),'DP Fcast (2)'[Week_final]= w_previous&&'DP Fcast (2)'[Location]=MAX('DP Fcast (2)'[Location])),[Fcast Volume(in EA) by Location])

        var diff = diff1 - diff2
        var total = (diff1 + diff2) / 2
        var diff_perc = diff / total
        var result = diff_perc
        return result