Difference between quantities from different filename

[AndoniA]

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 and the current one for each year. The value of the file is given by a Slicer.

This is the table. So, the result, for example for the first two lines, shoud be:

0	0	0	0	0	0
0	0	4.029	-5.379	-5.046	0

The operation is, from the higher filename, for each combination of Nameplate-Source plant, for example for 26/27: 9682 - 5653

Is there any option to do it?

Thanks ❤️

[Rupak_bi · Accepted solution]

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.

[Anonymous · Accepted solution]

Hi Rupak_bi ,thanks for the quick reply, I'll add more.

Hi @AndoniA ,

Please check the attachments to see if  meet your needs.

Best Regards

[AndoniA]

Thanks you very much to you and @Rupak_bi !
I finally managed to do it with:

_TEST 28/29 =

VAR _selectedMonth = MAX('IHS_all_Quantities_FY'[DateFilename]) 

VAR _previousMonth = CALCULATE(  

    MAX('IHS_all_Quantities_FY'[DateFilename]),  

    'IHS_all_Quantities_FY'[DateFilename] < _selectedMonth  

) 

VAR _currentValue = CALCULATE(

    SUM('IHS_all_Quantities_FY'[Quantity FY 28/29]),

    'IHS_all_Quantities_FY'[DateFilename] = _selectedMonth

)

VAR _previousValue = CALCULATE(

    SUM('IHS_all_Quantities_FY'[Quantity FY 28/29]),

    'IHS_all_Quantities_FY'[DateFilename] = _previousMonth

)

RETURN _currentValue - _previousValue
I used a Date form columns for Filename and works nice.
Thank you ❤️

[AndoniA]

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 · Accepted solution]

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.

[Rupak_bi]

Hi @AndoniA ,

this can be done by the steps below

1. create a new cal column and convert the "filename" values as date.

2. create another cal column and calculate value difference of current row and previous row indexed by date groupped by name plate and source plant.

this will give you the desired result in a column. 

if still have confusion, please share the table data in plain text (not photo) so that we can workout.