Forum Discussion
DAX for a difference between 2 month
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.
Super UserHi 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.