Assistance with DAX Calculation for Previous Month Values

[FarhanJeelani]

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
)

[tamerj1]

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?

[bhanu_gautam]

@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]
)

[FarhanJeelani]

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

[Anonymous]

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