Forum Discussion
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 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 ❤️
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 DAXdifference = 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]))RETURNif(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]))RETURNif(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]))RETURNif(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]))RETURNif(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]))RETURNif(Prev_Value=BLANK(),0,current_value-Prev_Value))If this works, Please accept as 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
5 Replies
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.
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
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 ❤️
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;62Hi 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 DAXdifference = 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]))RETURNif(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]))RETURNif(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]))RETURNif(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]))RETURNif(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]))RETURNif(Prev_Value=BLANK(),0,current_value-Prev_Value))If this works, Please accept as solution.
