topic Re: How do I group for a prior month and current month in DAX Commands and Tips

How do I group for a prior month and current month

EaglesTony — Wed, 21 Jan 2026 13:04:06 GMT

Hi,

I have the following data in a table called ApplicationMonthlySnapshot:

Title ReportingDate TotalA TotalB

ABC 12/5/2025 3 4

ABC 12/22/2025 3 5

ABC 12/30/2025 4 6

DEF 12/5/2025 1 2

DEF 12/30/2025 2 3

ABC 1/2/2026 4 7

ABC 1/9/2026 4 8

DEF 1/2/2026 3 4

DEF 1/9/2026 4 4

What I need is based on the current date:

1) Get the prior month (12) and the max record for each Title and sum up the individual colums, so for 12/2026 I would get:

ReportingDate TotalA TotalB

12/2025 6 9

This is based on the following max records for the month:

ABC 12/30/2025 4 6

DEF 12/30/2025 2 3

2) For the current month (01) and the max record for each Title and sum up the individual colums, so for 01/2026 I would get:

ReportingDate TotalA TotalB

01/2026 8 12

Title ReportingDate TotalA TotalB

ABC 1/9/2026 4 8

DEF 1/9/2026 4 4

Re: How do I group for a prior month and current month

FreemanZ — Wed, 21 Jan 2026 14:53:34 GMT

hi EaglesTony ,

Not sure if i fully get you, you can plot a table visual with data[reportingdate] column and two measures like below:

MaxTotalA = VAR _date = MAX(data[ReportingDate]) VAR _table1 = FILTER( ALL(data), data[ReportingDate]>=EDATE(_date, -12) && data[ReportingDate]<=_date ) VAR _table2 = ADDCOLUMNS( SUMMARIZE(_table1, data[Title]), "_MaxTotalA", CALCULATE(MAX(data[TotalA])) ) VAR _result = SUMX(_table2, [_MaxTotalA]) RETURN _result

MaxTotalB = VAR _date = MAX(data[ReportingDate]) VAR _table1 = FILTER( ALL(data), data[ReportingDate]>=EDATE(_date, -12) && data[ReportingDate]<=_date ) VAR _table2 = ADDCOLUMNS( SUMMARIZE(_table1, data[Title]), "_MaxTotalB", CALCULATE(MAX(data[TotalB])) ) VAR _result = SUMX(_table2, [_MaxTotalB]) RETURN _result

it works like:

Re: How do I group for a prior month and current month

Jihwan_Kim — Thu, 22 Jan 2026 04:02:45 GMT

Hi,

I am not sure how your semantic model looks like, but I tried to create the data model like below.

Please check the below picture and the attached pbix file.

In the DAX measure, I tried to use INDEX DAX function.

INDEX function (DAX) - DAX | Microsoft Learn

Sum of MaxTotal: = VAR _currentmonth = EOMONTH ( TODAY (), 0 ) VAR _priormonth = EOMONTH ( _currentmonth, -1 ) RETURN SUMX ( VALUES ( 'Calendar'[Month-Year] ), CALCULATE ( CALCULATE ( SUM ( 'Application'[Total] ), INDEX ( 1, SUMMARIZECOLUMNS ( 'Calendar'[Date], Title[Title], FILTER ( 'Calendar', 'Calendar'[Month-Year sort] <= _currentmonth && 'Calendar'[Month-Year sort] >= _priormonth ) ), ORDERBY ( CALCULATE ( SUM ( 'Application'[Total] ) ), DESC ), , PARTITIONBY ( Title[Title] ) ) ) ) )

Re: How do I group for a prior month and current month

v-ssriganesh — Tue, 27 Jan 2026 04:43:07 GMT

Hi EaglesTony,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to FreemanZ & Jihwan_Kim for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

Re: How do I group for a prior month and current month

Olufemi7 — Tue, 27 Jan 2026 07:00:49 GMT

Hello EaglesTony,

You can do this in both Power BI (DAX) and Microsoft Fabric (SQL). The logic is the same:

For each month, take the latest record per Title (using MAX or ROW_NUMBER()),
Then sum the totals across Titles.

LatestPerTitle = ADDCOLUMNS ( SUMMARIZE ( ApplicationMonthlySnapshot, ApplicationMonthlySnapshot[Title], FORMAT ( ApplicationMonthlySnapshot[ReportingDate], "MM/yyyy" ) ), "MaxDate", CALCULATE ( MAX ( ApplicationMonthlySnapshot[ReportingDate] ) ) ) MonthlyTotals = SUMMARIZE ( FILTER ( ApplicationMonthlySnapshot, ApplicationMonthlySnapshot[ReportingDate] IN VALUES ( LatestPerTitle[MaxDate] ) ), FORMAT ( ApplicationMonthlySnapshot[ReportingDate], "MM/yyyy" ), "TotalA", SUM ( ApplicationMonthlySnapshot[TotalA] ), "TotalB", SUM ( ApplicationMonthlySnapshot[TotalB] ) )

You can then filter for EOMONTH(TODAY(), -1) (prior month) and EOMONTH(TODAY(), 0) (current month).

Microsoft Docs:
SUMMARIZE (DAX)
CALCULATE (DAX)
Time intelligence functions (DAX)

Microsoft Fabric (SQL)

Using ROW_NUMBER():

WITH LatestPerTitle AS ( SELECT Title, FORMAT(ReportingDate, 'MM/yyyy') AS MonthYear, ReportingDate, TotalA, TotalB, ROW_NUMBER() OVER ( PARTITION BY Title, FORMAT(ReportingDate, 'MM/yyyy') ORDER BY ReportingDate DESC ) AS rn FROM ApplicationMonthlySnapshot ) SELECT MonthYear, SUM(TotalA) AS TotalA, SUM(TotalB) AS TotalB FROM LatestPerTitle WHERE rn = 1 AND MonthYear IN ( FORMAT(GETDATE(), 'MM/yyyy'), FORMAT(DATEADD(MONTH, -1, GETDATE()), 'MM/yyyy') ) GROUP BY MonthYear ORDER BY MonthYear;

Or without window functions (JOIN + MAX):

WITH MaxDates AS ( SELECT Title, FORMAT(ReportingDate, 'MM/yyyy') AS MonthYear, MAX(ReportingDate) AS MaxDate FROM ApplicationMonthlySnapshot GROUP BY Title, FORMAT(ReportingDate, 'MM/yyyy') ) SELECT m.MonthYear, SUM(s.TotalA) AS TotalA, SUM(s.TotalB) AS TotalB FROM MaxDates m JOIN ApplicationMonthlySnapshot s ON s.Title = m.Title AND FORMAT(s.ReportingDate, 'MM/yyyy') = m.MonthYear AND s.ReportingDate = m.MaxDate WHERE m.MonthYear IN ( FORMAT(GETDATE(), 'MM/yyyy'), FORMAT(DATEADD(MONTH, -1, GETDATE()), 'MM/yyyy') ) GROUP BY m.MonthYear ORDER BY m.MonthYear;

Microsoft Docs:
FORMAT (Transact-SQL)
DATEADD (Transact-SQL)
ROW_NUMBER (Transact-SQL)

This produces exactly what you described:

December 2025 → TotalA=6, TotalB=9
January 2026 → TotalA=8, TotalB=12

Re: How do I group for a prior month and current month

v-ssriganesh — Fri, 30 Jan 2026 10:40:23 GMT

Hello EaglesTony,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.