DAX for a difference between 2 month

[Shravan16]

Hi, 

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

	Jan-24	Difference	Feb-24	Difference
A	100	50	175	75
B	200	20	210	10
C	300	30	360	60
D	400	40	480	80

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-24	Feb-24	Diff
A	100	175	75
B	200	210	10
C	300	360	60
D	400	480	80

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. 

[rajendraongole1]

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]

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]

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.