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.
Rupak_bi's avatar
Rupak_bi
Super User
1 year ago

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.