DAX Measures not Appearing in Matrix

[TheoA1]

I have created a few DAX measures to calculate the current month's reporting period totals, the previous month's reporting period totals, and then the difference between the two. The measures appear to all be working perfectly. However, when I throw them into a matrix, not all the rows show up for the previous reporting month totals.

I have attached a sample of what I have been seeing:

What appears to be happening is the matrix is only showing rows for values that appear in the "Reporting Month" column. However, I would like to see the additional row in the "Previous Reporting Month" that accounts for the additional $100,000 that falls under a different issue type than "Other" that is not displaying in the matrix. As you can see, the subtotal is calculating properly because the $200,000 is correct but I want to see the "Issue" for the additional $100,000 in "Previous Reporting Month" and then see $0 in the "Reporting Month" so that my viewers can have full visibility of all the changes.

Below are my current DAX measures:

"Reporting Month" column

Latest Month =

CALCULATE(SUM('Current Combined Data'[Aged Total]),YEAR('Current Combined Data'[Reporting Month]) = YEAR(MAX('Current Combined Data'[Reporting Month])),MONTH('Current Combined Data'[Reporting Month]) = MONTH(MAX('Current Combined Data'[Reporting Month])))

"Previous Reporting Month" column

Previous Month =

    var _currentMonth = MONTH(MAX('Current Combined Data'[Reporting Month]))

    var _currentYear = YEAR(MAX('Current Combined Data'[Reporting Month]))

    var _previousMonth = IF(_currentMonth = 1, 12, _currentMonth-1)

    var _previousYear = IF(_currentMonth=1, _currentYear-1, _currentYear)

return

    CALCULATE(SUM('Current Combined Data'[Aged Total]),MONTH('Current Combined Data'[Reporting Month]) = _previousMonth, YEAR('Current Combined Data'[Reporting Month]) = _previousYear)

"Difference" column

Latest vs Previous Diff = [Latest Month] - [Previous Month]

Any help on getting this visibility added is greatly appreciated. Thanks!

[tamerj1]

Hi @TheoA1 

This is not easy without testing in a sample file. There is a 20% chance the following works

Previous Month =
VAR PreviousMonth =
FORMAT (
EOMONTH ( MAX ( 'Current Combined Data'[Reporting Month] ), -1 ),
"YYYYMM"
)
VAR PreviousIssues =
CALCULATETABLE (
VALUES ( 'Current Combined Data'[Issue] ),
ALLSELECTED ( 'Current Combined Data'[Issue] ),
FORMAT ( 'Current Combined Data'[Reporting Month], "YYYYMM" ) = PreviousMonth
)
VAR Result =
CALCULATE (
VALUES ( 'Current Combined Data'[Issue] ),
FORMAT ( 'Current Combined Data'[Reporting Month], "YYYYMM" ) = PreviousMonth,
KEEPFILTERS ( TREATAS ( PreviousIssues, 'Current Combined Data'[Issue] ) )
)
RETURN
Result

[Anonymous]

Hi @TheoA1 

Thanks to tamerj1‘s attention to this thread.

I would like to ask if tamerj1's reply has helped you solve your problem? If so, please consider accepting his reply as the solution, which will make it easier for other users who encounter the same problem to find a solution.

If the problem is still not solved, please provide sample data that can fully cover your problem. It would be best if you can provide a pbix file. (Please remove private information)

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

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.