How to get dynamically last to Current month 1st to Max date KPIs calc for Each Month In Matrix?

[Roshan_201295]

Hi There, 

I am getting an issue while solving problem for calculating Sum of accounts from current month and last months form the dates (1 to Max(date)-1).

I working on matrix visual and Matrix visual contains businesses names at the Row Header and Column header contains the DISB_MONTH. I would like to get the current month (1st Aug , Max-1 Aug ) and last month (1st , Max-1 ) but & for last months its should work for each last months like Jul,jun,may,apr,mar,feb,jan.
I tring but showing blank and not dynamically spreaded accross this criteria like (1st to Max)
For ur references I providing an image
Please help me in that deadline is very close 🙏

[v-saisrao-msft]

Hi @Roshan_201295,

We haven’t heard back from you in a while regarding your issue. let us know if your issue has been resolved or if you still require support.

Thank you.

[v-saisrao-msft]

Hi @Roshan_201295,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

[v-saisrao-msft]

Hi @Roshan_201295,

Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further.

Thank you.

[v-saisrao-msft · Accepted solution]

Hi @Roshan_201295,

Thank you @Shahid12523 @rohit1991, for your insights.

I have replicated your scenario using sample data from my end and obtained the following output. I've attached the pbix file for your reference.

I hope this is helpful.

Thank you.

[Shahid12523]

Use a dynamic DAX measure like this

LMTD Accounts =
VAR _MonthStart = STARTOFMONTH('Date'[Date])
VAR _MonthEnd =
MIN(
MAX('Date'[Date]) - 1,
ENDOFMONTH('Date'[Date])
)
RETURN
CALCULATE(
SUM(Accounts[Amount]),
DATESBETWEEN('Date'[Date], _MonthStart, _MonthEnd)
)

[rohit1991]

Hi @Roshan_201295 

Could you Please follow the Steps below:

Create a proper Date table and mark it as a Date table.

Build a measure for Accounts = SUM(Fact_Disbursements[Accounts])

Create an Anchor Day measure:

Anchor Date = CALCULATE(MAX(Fact_Disbursements[Disb_Date]), ALL(Dim_Date))

Anchor Day (minus 1) = DAY([Anchor Date]) - 1

MTD measure:

MTD (1..Max-1) =
VAR AnchorDay = [Anchor Day (minus 1)]
VAR MonthStart = DATE(SELECTEDVALUE(Dim_Date[Year]), SELECTEDVALUE(Dim_Date[MonthNumber]),1)
VAR MonthEnd   = EOMONTH(MonthStart,0)
VAR EndDate    = DATE(YEAR(MonthStart),MONTH(MonthStart),MIN(DAY(MonthEnd),AnchorDay))
RETURN CALCULATE([Accounts], DATESBETWEEN(Dim_Date[Date],MonthStart,EndDate))

LMTD measure (previous month):

LMTD (Prev Mth 1..Max-1) =
VAR AnchorDay = [Anchor Day (minus 1)]
VAR CurrMonthStart = DATE(SELECTEDVALUE(Dim_Date[Year]),SELECTEDVALUE(Dim_Date[MonthNumber]),1)
VAR PrevMonthStart = EOMONTH(CurrMonthStart,-1)+1
VAR PrevMonthEnd   = EOMONTH(PrevMonthStart,0)
VAR EndDate        = DATE(YEAR(PrevMonthStart),MONTH(PrevMonthStart),MIN(DAY(PrevMonthEnd),AnchorDay))
RETURN CALCULATE([Accounts],DATESBETWEEN(Dim_Date[Date],PrevMonthStart,EndDate))

Now, drop Business on Rows, MonthYear on Columns, and both measures in Values. The table will automatically calculate each month’s MTD (1 to Max-1) and the previous month’s LMTD dynamically. You can also add a simple

variance measure:

MoM Variance = [MTD (1..Max-1)] - [LMTD (Prev Mth 1..Max-1)]

This setup ensures your Matrix always respects the same cut-off day (Max-1), works across months, and stays dynamic as new data comes in.