How do I group for a prior month and current month

[EaglesTony]

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

[FreemanZ · Accepted solution]

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:

[v-ssriganesh]

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.

[Olufemi7]

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

[v-ssriganesh]

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.

[Jihwan_Kim · Accepted solution]

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

[FreemanZ · Accepted solution]

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: