Forum Discussion

AndoniA's avatar
AndoniA
Regular Visitor
1 year ago
Solved

Difference between quantities from different filename

Hello! As an example, I have a table showing the forecast for different cars. Each month, there is a file that gives that forecast. I want to compare the difference between the previous month's file...
Need Help
  • Rupak_bi's avatar
    Rupak_bi
    1 year ago

    Hi AndoniA ,

    Here Is your solution......

    this is the original table you have given

    And this is the calculated table developped

    And this is the DAX used to create the table.
    just go to modelling tab >>> create a new calculate table and insert below DAX

    difference = SUMMARIZE('Table','Table'[Model],'Table'[Plant],'Table'[date_filename],

    "FY 24/25 Diff",
    var current_value = calculate(max('Table'[FY 24/25]))
    Var Prev_Value = CALCULATE(max('Table'[FY 24/25]),ALLEXCEPT('Table','Table'[Model],'Table'[Plant]),'Table'[date_filename]<EARLIER('Table'[date_filename]))
    RETURN
    if(Prev_Value=BLANK(),0,current_value-Prev_Value),

    "FY 25/26 Diff",
    var current_value = calculate(max('Table'[FY 25/26]))
    Var Prev_Value = CALCULATE(max('Table'[FY 25/26]),ALLEXCEPT('Table','Table'[Model],'Table'[Plant]),'Table'[date_filename]<EARLIER('Table'[date_filename]))
    RETURN
    if(Prev_Value=BLANK(),0,current_value-Prev_Value),

    "FY 26/27 Diff",
    var current_value = calculate(max('Table'[FY 26/27]))
    Var Prev_Value = CALCULATE(max('Table'[FY 26/27]),ALLEXCEPT('Table','Table'[Model],'Table'[Plant]),'Table'[date_filename]<EARLIER('Table'[date_filename]))
    RETURN
    if(Prev_Value=BLANK(),0,current_value-Prev_Value),

    "FY 27/28 Diff",
    var current_value = calculate(max('Table'[FY 27/28]))
    Var Prev_Value = CALCULATE(max('Table'[FY 27/28]),ALLEXCEPT('Table','Table'[Model],'Table'[Plant]),'Table'[date_filename]<EARLIER('Table'[date_filename]))
    RETURN
    if(Prev_Value=BLANK(),0,current_value-Prev_Value),


    "FY 28/29 Diff",
    var current_value = calculate(max('Table'[FY 28/29]))
    Var Prev_Value = CALCULATE(max('Table'[FY 28/29]),ALLEXCEPT('Table','Table'[Model],'Table'[Plant]),'Table'[date_filename]<EARLIER('Table'[date_filename]))
    RETURN
    if(Prev_Value=BLANK(),0,current_value-Prev_Value)


    )
     
    If this works, Please accept as solution.
AndoniA's avatar
AndoniA
Regular Visitor
1 year ago

Hello!

Thank you for the prompt reply. I will pass you the table in text as I have not been able to do it.
Now filename is in date. The last 6 columns would be the expected result.

Thank you.

* The FY quantity columns are already in normal column and measure.

 

Model;Plant;date_filename;FY 24/25;FY 25/26;FY 26/27;FY 27/28;FY 28/29;FY 29/30;FY 2024/2025;FY 2025/2026;FY 2026/2027;FY 2027/2028;FY 2028/2029;FY 2029/2030
e-tron GT;Boellinger;2024/09;6102;5923;5653;5379;5046;0;0;0;0;0;0;0
e-tron GT;Boellinger;2024/10;6102;5923;9682;0;0;0;0;0;4029;-5379;-5046;0
Q7;Bratislava;2024/09;0;0;43824;59070;57091;54541;0;0;0;0;0;0
Q7;Bratislava;2024/10;0;0;43730;59650;59259;55711;0;0;-94;580;2168;1170
Q9;Bratislava;2024/09;0;0;13139;22943;20945;18385;0;0;0;0;0;0
Q9;Bratislava;2024/10;0;0;13140;22937;20975;18447;0;0;1;-6;30;62

Rupak_bi's avatar
Rupak_bi
Super User
1 year ago

Hi AndoniA ,

Here Is your solution......

this is the original table you have given

And this is the calculated table developped

And this is the DAX used to create the table.
just go to modelling tab >>> create a new calculate table and insert below DAX

difference = SUMMARIZE('Table','Table'[Model],'Table'[Plant],'Table'[date_filename],

"FY 24/25 Diff",
var current_value = calculate(max('Table'[FY 24/25]))
Var Prev_Value = CALCULATE(max('Table'[FY 24/25]),ALLEXCEPT('Table','Table'[Model],'Table'[Plant]),'Table'[date_filename]<EARLIER('Table'[date_filename]))
RETURN
if(Prev_Value=BLANK(),0,current_value-Prev_Value),

"FY 25/26 Diff",
var current_value = calculate(max('Table'[FY 25/26]))
Var Prev_Value = CALCULATE(max('Table'[FY 25/26]),ALLEXCEPT('Table','Table'[Model],'Table'[Plant]),'Table'[date_filename]<EARLIER('Table'[date_filename]))
RETURN
if(Prev_Value=BLANK(),0,current_value-Prev_Value),

"FY 26/27 Diff",
var current_value = calculate(max('Table'[FY 26/27]))
Var Prev_Value = CALCULATE(max('Table'[FY 26/27]),ALLEXCEPT('Table','Table'[Model],'Table'[Plant]),'Table'[date_filename]<EARLIER('Table'[date_filename]))
RETURN
if(Prev_Value=BLANK(),0,current_value-Prev_Value),

"FY 27/28 Diff",
var current_value = calculate(max('Table'[FY 27/28]))
Var Prev_Value = CALCULATE(max('Table'[FY 27/28]),ALLEXCEPT('Table','Table'[Model],'Table'[Plant]),'Table'[date_filename]<EARLIER('Table'[date_filename]))
RETURN
if(Prev_Value=BLANK(),0,current_value-Prev_Value),


"FY 28/29 Diff",
var current_value = calculate(max('Table'[FY 28/29]))
Var Prev_Value = CALCULATE(max('Table'[FY 28/29]),ALLEXCEPT('Table','Table'[Model],'Table'[Plant]),'Table'[date_filename]<EARLIER('Table'[date_filename]))
RETURN
if(Prev_Value=BLANK(),0,current_value-Prev_Value)


)
 
If this works, Please accept as solution.