Forum Discussion

Shravan16's avatar
Shravan16
New Member
1 year ago

DAX for a difference between 2 month

Hi, 

I need your help in writing a DAX. 
The DAX that i have written is giving me this output : 

 Jan-24DifferenceFeb-24Difference
A1005017575
B2002021010
C3003036060
D4004048080


However, i do not want the bolddifference to be calculated which is the difference between JAn and Dec figures. 
I am expecting this as result : 

 Jan-24Feb-24Diff
A10017575
B20021010
C30036060
D40048080


The  DAX needs to be dynamic and not limited to Jan and Feb only since each month i will select the last 2 months in my filter to have the difference.

Thank you. 

Need Help

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi Shravan16  -  your date table is properly set up with a continuous date range and is marked as a date table in Power BI.

    create a measure as below:

    LastTwoMonthsDiff =
    VAR SelectedMonths = DISTINCT(VALUES('DateTable'[MonthYear]))
    VAR LastMonth = MAX('DateTable'[MonthYear])
    VAR SecondLastMonth = CALCULATE(MAX('DateTable'[MonthYear]), 'DateTable'[MonthYear] < LastMonth)
    RETURN
    IF(
    COUNTROWS(SelectedMonths) = 2,
    SUMX(
    VALUES(FactTable[Category]),
    CALCULATE(SUM(FactTable[Value]), 'DateTable'[MonthYear] = LastMonth) -
    CALCULATE(SUM(FactTable[Value]), 'DateTable'[MonthYear] = SecondLastMonth)
    ),
    BLANK() -- Ensures measure only calculates when exactly two months are selected
    )

     

    replace with your table names as per your model . please check and confirm.

    • Shravan16's avatar
      Shravan16
      New Member
      1 year ago

      Hi Rajen. 

      I have tried the DAX works but returns the value as blank. 
      This is the current DAX suggested by you, 
      LastTwoMonthsDiff =
      VAR SelectedMonths = DISTINCT(VALUES('FI_Files'[Reporting Date]))
      VAR LastMonth = MAX('FI_Files'[Reporting Date])
      VAR SecondLastMonth = CALCULATE(
      MAX('FI_Files'[Reporting Date]),
      'FI_Files'[Reporting Date] < LastMonth
      )
      RETURN
      IF(
      COUNTROWS(SelectedMonths) = 2,
      SUMX(
      VALUES(FI_Files[Book Classification]),
      CALCULATE(SUM(FI_Files[Nominal (MUR)]), 'FI_Files'[Reporting Date] = LastMonth) -
      CALCULATE(SUM(FI_Files[Nominal (MUR)]), 'FI_Files'[Reporting Date] = SecondLastMonth)
      ),
      BLANK() -- Ensures measure only calculates when exactly two months are selected
      )
      Below is the current DAX i am using that is giving me difference for all months and not only for the last 2 months selected. 

      Nominal Difference MoM =
      VAR CurrentMonthNominal =
      SUM('FI_Files'[Nominal (MUR)])

      VAR PreviousMonthNominal =
      CALCULATE(
      SUM('FI_Files'[Nominal (MUR)]),
      PREVIOUSMONTH('FI_Files'[Reporting Date])
      )

      RETURN
      CurrentMonthNominal - PreviousMonthNominal

      Thank you. 

      • FarhanJeelani's avatar
        FarhanJeelani
        Super User
        1 year ago

        Hi Shravan16 ,

        The key here is to dynamically detect the last two selected months and ensure the calculation is only based on those two months.

         

        Please try below dax

        LastTwoMonthsDiff =
        VAR SelectedMonths = VALUES('FI_Files'[Reporting Date]) -- Get all selected months
        VAR MaxMonth = MAX(SelectedMonths) -- Get the most recent month
        VAR MinMonth = CALCULATE(MIN(SelectedMonths), SelectedMonths < MaxMonth) -- Get the second most recent month
        VAR CurrentMonthNominal = CALCULATE(SUM('FI_Files'[Nominal (MUR)]), 'FI_Files'[Reporting Date] = MaxMonth)
        VAR PreviousMonthNominal = CALCULATE(SUM('FI_Files'[Nominal (MUR)]), 'FI_Files'[Reporting Date] = MinMonth)

        RETURN
        IF (
        COUNTROWS(SelectedMonths) = 2, -- Ensure only two months are selected
        CurrentMonthNominal - PreviousMonthNominal, -- Return the difference
        BLANK() -- Return blank if more or less than 2 months are selected
        )

         

        This measure should give you the desired result, where it calculates the difference only for the last two selected months in your filter.

        If you select Feb-24 and Mar-24, it will calculate the difference for those two months instead.

         

        Please mark this as solution if it helps you. Appreciate Kudos.