topic Re: Assistance with DAX Calculation for Previous Month Values in DAX Commands and Tips

Assistance with DAX Calculation for Previous Month Values

FarhanJeelani — Thu, 02 Jan 2025 11:08:37 GMT

Hi Community Members,

I’m using the following DAX to calculate the value for the previous month. If the previous month's value is unavailable, it should default to the second previous month's value, and this logic should continue for up to 13 months to retrieve the correct values.

The DAX below works fine for individual calculations, but I’m encountering an issue where the total is not adding up correctly.

Value Last 13 Months Previous Month = SUMX( VALUES('Calendar'[Date]), VAR Month1 = CALCULATE ( SUM ( vw_Report_1241[Total_AM] ), PREVIOUSMONTH ( 'Calendar'[Date] ) ) VAR Month2 = CALCULATE ( SUM ( vw_Report_1241[Total_AM] ), DATEADD ( 'Calendar'[Date], -2, MONTH ) ) VAR Month3 = CALCULATE ( SUM ( vw_Report_1241[Total_AM] ), DATEADD ( 'Calendar'[Date], -3, MONTH ) ) VAR Month4 = CALCULATE ( SUM ( vw_Report_1241[Total_AM] ), DATEADD ( 'Calendar'[Date], -4, MONTH ) ) VAR Month5 = CALCULATE ( SUM ( vw_Report_1241[Total_AM] ), DATEADD ( 'Calendar'[Date], -5, MONTH ) ) VAR Month6 = CALCULATE ( SUM ( vw_Report_1241[Total_AM] ), DATEADD ( 'Calendar'[Date], -6, MONTH ) ) VAR Month7 = CALCULATE ( SUM ( vw_Report_1241[Total_AM] ), DATEADD ( 'Calendar'[Date], -7, MONTH ) ) VAR Month8 = CALCULATE ( SUM ( vw_Report_1241[Total_AM] ), DATEADD ( 'Calendar'[Date], -8, MONTH ) ) VAR Month9 = CALCULATE ( SUM ( vw_Report_1241[Total_AM] ), DATEADD ( 'Calendar'[Date], -9, MONTH ) ) VAR Month10 = CALCULATE ( SUM ( vw_Report_1241[Total_AM] ), DATEADD ( 'Calendar'[Date], -10, MONTH ) ) VAR Month11 = CALCULATE ( SUM ( vw_Report_1241[Total_AM] ), DATEADD ( 'Calendar'[Date], -11, MONTH ) ) VAR Month12 = CALCULATE ( SUM ( vw_Report_1241[Total_AM] ), DATEADD ( 'Calendar'[Date], -12, MONTH ) ) VAR Month13 = CALCULATE ( SUM ( vw_Report_1241[Total_AM] ), DATEADD ( 'Calendar'[Date], -13, MONTH ) ) VAR Result = IF ( NOT ISBLANK ( Month1 ), Month1, IF ( NOT ISBLANK ( Month2 ), Month2, IF ( NOT ISBLANK ( Month3 ), Month3, IF ( NOT ISBLANK ( Month4 ), Month4, IF ( NOT ISBLANK ( Month5 ), Month5, IF ( NOT ISBLANK ( Month6 ), Month6, IF ( NOT ISBLANK ( Month7 ), Month7, IF ( NOT ISBLANK ( Month8 ), Month8, IF ( NOT ISBLANK ( Month9 ), Month9, IF ( NOT ISBLANK ( Month10 ), Month10, IF ( NOT ISBLANK ( Month11 ), Month11, IF ( NOT ISBLANK ( Month12 ), Month12, Month13 ) ) ) ) ) ) ) ) ) ) ) ) RETURN Result )

Re: Assistance with DAX Calculation for Previous Month Values

bhanu_gautam — Thu, 02 Jan 2025 11:32:03 GMT

FarhanJeelani , Try using

DAX
Value Last 13 Months Previous Month =
SUMX(
SUMMARIZE(
'Calendar',
'Calendar'[Date],
"Result",
VAR Month1 = CALCULATE(SUM(vw_Report_1241[Total_AM]), PREVIOUSMONTH('Calendar'[Date]))
VAR Month2 = CALCULATE(SUM(vw_Report_1241[Total_AM]), DATEADD('Calendar'[Date], -2, MONTH))
VAR Month3 = CALCULATE(SUM(vw_Report_1241[Total_AM]), DATEADD('Calendar'[Date], -3, MONTH))
VAR Month4 = CALCULATE(SUM(vw_Report_1241[Total_AM]), DATEADD('Calendar'[Date], -4, MONTH))
VAR Month5 = CALCULATE(SUM(vw_Report_1241[Total_AM]), DATEADD('Calendar'[Date], -5, MONTH))
VAR Month6 = CALCULATE(SUM(vw_Report_1241[Total_AM]), DATEADD('Calendar'[Date], -6, MONTH))
VAR Month7 = CALCULATE(SUM(vw_Report_1241[Total_AM]), DATEADD('Calendar'[Date], -7, MONTH))
VAR Month8 = CALCULATE(SUM(vw_Report_1241[Total_AM]), DATEADD('Calendar'[Date], -8, MONTH))
VAR Month9 = CALCULATE(SUM(vw_Report_1241[Total_AM]), DATEADD('Calendar'[Date], -9, MONTH))
VAR Month10 = CALCULATE(SUM(vw_Report_1241[Total_AM]), DATEADD('Calendar'[Date], -10, MONTH))
VAR Month11 = CALCULATE(SUM(vw_Report_1241[Total_AM]), DATEADD('Calendar'[Date], -11, MONTH))
VAR Month12 = CALCULATE(SUM(vw_Report_1241[Total_AM]), DATEADD('Calendar'[Date], -12, MONTH))
VAR Month13 = CALCULATE(SUM(vw_Report_1241[Total_AM]), DATEADD('Calendar'[Date], -13, MONTH))
RETURN
IF(
NOT ISBLANK(Month1), Month1,
IF(
NOT ISBLANK(Month2), Month2,
IF(
NOT ISBLANK(Month3), Month3,
IF(
NOT ISBLANK(Month4), Month4,
IF(
NOT ISBLANK(Month5), Month5,
IF(
NOT ISBLANK(Month6), Month6,
IF(
NOT ISBLANK(Month7), Month7,
IF(
NOT ISBLANK(Month8), Month8,
IF(
NOT ISBLANK(Month9), Month9,
IF(
NOT ISBLANK(Month10), Month10,
IF(
NOT ISBLANK(Month11), Month11,
IF(
NOT ISBLANK(Month12), Month12,
Month13
)
)
)
)
)
)
)
)
)
)
)
)
),
[Result]
)

Re: Assistance with DAX Calculation for Previous Month Values

FarhanJeelani — Thu, 02 Jan 2025 12:39:36 GMT

This is not working. The number is appearing on rows but not summing up on total.

Re: Assistance with DAX Calculation for Previous Month Values

tamerj1 — Thu, 02 Jan 2025 13:46:20 GMT

Hi FarhanJeelani
It depends on the context of the visual where you place the measure in. What does each row in the table visual represent?

Re: Assistance with DAX Calculation for Previous Month Values

Anonymous — Fri, 03 Jan 2025 03:15:15 GMT

Hi FarhanJeelani ,

What fields are you using in the visual object? Is it the 'Date' or 'Month' column? Can you provide some simple data or screenshots?

If you are unsure how to upload data please refer to

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards