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

    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 ❤️