topic Complex measure not summing correctly in matrix visual, nor by year in DAX Commands and Tips

Complex measure not summing correctly in matrix visual, nor by year

schaezac — Tue, 08 Apr 2025 19:04:12 GMT

Hi, I have a measure (DAX below) that is essentially calculating in-month revenue by subtracting prior month period-to-date revenue from current month period-to-date revenue (unfortunately there is no way around doing it this way). The calculation for each of those period-to-date revenues itself is also somewhat complex.

Norm. MTD Earned Rev. = VAR PTDHindsightEarnedRev = VAR md = DATE(year(MAXX(ALLSELECTED('Date'[Date]),'Date'[Date])),month(MAXX(ALLSELECTED('Date'[Date]),'Date'[Date])),1) RETURN SUMX('WIP All Periods DB V3', VAR actualptdcost = [PTD Cost] VAR hindsightrev = calculate(MAX('WIP All Periods DB V3'[Archived Budget Revenue]),ALLEXCEPT('WIP All Periods DB V3','WIP All Periods DB V3'[ProjectID]),'Date'[Date]=md) VAR hindsightcost = calculate(MAX('WIP All Periods DB V3'[Archived Budget Cost]),ALLEXCEPT('WIP All Periods DB V3','WIP All Periods DB V3'[ProjectID]),'Date'[Date]=md) VAR normpctcomplete = DIVIDE(actualptdcost,hindsightcost,0) RETURN normpctcomplete * hindsightrev) VAR PriorMonthPTDHindsightEarnedRev = VAR pmmd = DATE(year(MAXX(ALLSELECTED('Date'[Date]),'Date'[Date])),month(MAXX(ALLSELECTED('Date'[Date]),'Date'[Date])),1) RETURN CALCULATE( SUMX('WIP All Periods DB V3', VAR pmactualptdcost = [PTD Cost] VAR pmhindsightrev = calculate(MAX('WIP All Periods DB V3'[Archived Budget Revenue]),ALLEXCEPT('WIP All Periods DB V3','WIP All Periods DB V3'[ProjectID]),'Date'[Date]=pmmd) VAR pmhindsightcost = calculate(MAX('WIP All Periods DB V3'[Archived Budget Cost]),ALLEXCEPT('WIP All Periods DB V3','WIP All Periods DB V3'[ProjectID]),'Date'[Date]=pmmd) VAR pmnormpctcomplete = DIVIDE(pmactualptdcost,pmhindsightcost,0) RETURN pmnormpctcomplete * pmhindsightrev), PREVIOUSMONTH('Date'[Date])) RETURN PTDHindsightEarnedRev - PriorMonthPTDHindsightEarnedRev

This measure is returning the expected values by month, but is way off annually, and fails to sum months correctly across a matrix visual. I should mention I do have a date table in the data model.

I have tried creating a separate SUMX measure to aggregate the first, but it is not working. The values are way too high.

Any advice would be appreciated - thanks!

Re: Complex measure not summing correctly in matrix visual, nor by year

DataInsights — Tue, 08 Apr 2025 20:43:01 GMT

schaezac,

Here's an idea that assumes the visual grain is year/month and the date table contains a column Month Start Date (2024-03-01, 2024-04-01, etc.). If you could provide a sample pbix, it would facilitate testing.

Norm. MTD Earned Rev. = VAR md = DATE ( YEAR ( MAXX ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] ) ), MONTH ( MAXX ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] ) ), 1 ) VAR Table = ADDCOLUMNS ( VALUES ( 'Date'[Month Start Date] ), "@PTDHindsightEarnedRev", CALCULATE ( SUMX ( 'WIP All Periods DB V3', VAR actualptdcost = [PTD Cost] VAR hindsightrev = CALCULATE ( MAX ( 'WIP All Periods DB V3'[Archived Budget Revenue] ), ALLEXCEPT ( 'WIP All Periods DB V3', 'WIP All Periods DB V3'[ProjectID] ) ) VAR hindsightcost = CALCULATE ( MAX ( 'WIP All Periods DB V3'[Archived Budget Cost] ), ALLEXCEPT ( 'WIP All Periods DB V3', 'WIP All Periods DB V3'[ProjectID] ) ) VAR normpctcomplete = DIVIDE ( actualptdcost, hindsightcost, 0 ) RETURN normpctcomplete * hindsightrev ) ), "@PriorMonthPTDHindsightEarnedRev", CALCULATE ( SUMX ( 'WIP All Periods DB V3', VAR pmactualptdcost = [PTD Cost] VAR pmhindsightrev = CALCULATE ( MAX ( 'WIP All Periods DB V3'[Archived Budget Revenue] ), ALLEXCEPT ( 'WIP All Periods DB V3', 'WIP All Periods DB V3'[ProjectID] ) ) VAR pmhindsightcost = CALCULATE ( MAX ( 'WIP All Periods DB V3'[Archived Budget Cost] ), ALLEXCEPT ( 'WIP All Periods DB V3', 'WIP All Periods DB V3'[ProjectID] ) ) VAR pmnormpctcomplete = DIVIDE ( pmactualptdcost, pmhindsightcost, 0 ) RETURN pmnormpctcomplete * pmhindsightrev ), DATEADD ( 'Date'[Date], -1, MONTH ) ) ) VAR Result = SUMX ( Table, [@PTDHindsightEarnedRev] - [@PriorMonthPTDHindsightEarnedRev] ) RETURN Result

Re: Complex measure not summing correctly in matrix visual, nor by year

schaezac — Tue, 08 Apr 2025 21:01:26 GMT

Thanks for your help - I'm getting the following error. I will try to create a sample pbix tomorrow morning (will have to create it from scratch)

The syntax for 'Table' is incorrect. (DAX(VAR md = DATE ( YEAR ( MAXX ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] ) ), MONTH ( MAXX ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] ) ), 1 )VAR Table = ADDCOLUMNS ( VALUES ( 'Date'[Month Start Date] ), "@PTDHindsightEarnedRev", CALCULATE ( SUMX ( 'WIP All Periods DB V3', VAR actualptdcost = [PTD Cost] VAR hindsightrev = CALCULATE ( MAX ( 'WIP All Periods DB V3'[Archived Budget Revenue] ), ALLEXCEPT ( 'WIP All Periods DB V3', 'WIP All Periods DB V3'[ProjectID] ) ) VAR hindsightcost = CALCULATE ( MAX ( 'WIP All Periods DB V3'[Archived Budget Cost] ), ALLEXCEPT ( 'WIP All Periods DB V3', 'WIP All Periods DB V3'[ProjectID] ) ) VAR normpctcomplete = DIVIDE ( actualptdcost, hindsightcost, 0 ) RETURN normpctcomplete * hindsightrev ) ), "@PriorMonthPTDHindsightEarnedRev", CALCULATE ( SUMX ( 'WIP All Periods DB V3', VAR pmactualptdcost = [PTD Cost] VAR pmhindsightrev = CALCULATE ( MAX ( 'WIP All Periods DB V3'[Archived Budget Revenue] ), ALLEXCEPT ( 'WIP All Periods DB V3', 'WIP All Periods DB V3'[ProjectID] ) ) VAR pmhindsightcost = CALCULATE ( MAX ( 'WIP All Periods DB V3'[Archived Budget Cost] ), ALLEXCEPT ( 'WIP All Periods DB V3', 'WIP All Periods DB V3'[ProjectID] ) ) VAR pmnormpctcomplete = DIVIDE ( pmactualptdcost, pmhindsightcost, 0 ) RETURN pmnormpctcomplete * pmhindsightrev ), DATEADD ( 'Date'[Date], -1, MONTH ) ) )VAR Result = SUMX ( Table, [@PTDHindsightEarnedRev] - [@PriorMonthPTDHindsightEarnedRev] )RETURN Result)).

Re: Complex measure not summing correctly in matrix visual, nor by year

DataInsights — Tue, 08 Apr 2025 21:14:35 GMT

schaezac,

Easy fix--"Table" is a reserved word so I renamed my variable. 🙂

Norm. MTD Earned Rev. = VAR md = DATE ( YEAR ( MAXX ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] ) ), MONTH ( MAXX ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] ) ), 1 ) VAR RevTable = ADDCOLUMNS ( VALUES ( 'Date'[Month Start Date] ), "@PTDHindsightEarnedRev", CALCULATE ( SUMX ( 'WIP All Periods DB V3', VAR actualptdcost = [PTD Cost] VAR hindsightrev = CALCULATE ( MAX ( 'WIP All Periods DB V3'[Archived Budget Revenue] ), ALLEXCEPT ( 'WIP All Periods DB V3', 'WIP All Periods DB V3'[ProjectID] ) ) VAR hindsightcost = CALCULATE ( MAX ( 'WIP All Periods DB V3'[Archived Budget Cost] ), ALLEXCEPT ( 'WIP All Periods DB V3', 'WIP All Periods DB V3'[ProjectID] ) ) VAR normpctcomplete = DIVIDE ( actualptdcost, hindsightcost, 0 ) RETURN normpctcomplete * hindsightrev ) ), "@PriorMonthPTDHindsightEarnedRev", CALCULATE ( SUMX ( 'WIP All Periods DB V3', VAR pmactualptdcost = [PTD Cost] VAR pmhindsightrev = CALCULATE ( MAX ( 'WIP All Periods DB V3'[Archived Budget Revenue] ), ALLEXCEPT ( 'WIP All Periods DB V3', 'WIP All Periods DB V3'[ProjectID] ) ) VAR pmhindsightcost = CALCULATE ( MAX ( 'WIP All Periods DB V3'[Archived Budget Cost] ), ALLEXCEPT ( 'WIP All Periods DB V3', 'WIP All Periods DB V3'[ProjectID] ) ) VAR pmnormpctcomplete = DIVIDE ( pmactualptdcost, pmhindsightcost, 0 ) RETURN pmnormpctcomplete * pmhindsightrev ), DATEADD ( 'Date'[Date], -1, MONTH ) ) ) VAR Result = SUMX ( RevTable, [@PTDHindsightEarnedRev] - [@PriorMonthPTDHindsightEarnedRev] ) RETURN Result

Re: Complex measure not summing correctly in matrix visual, nor by year

schaezac — Tue, 08 Apr 2025 21:26:16 GMT

Amazing. Works perfectly and I learned a whole new approach to this. THANK YOU!!!

Re: Complex measure not summing correctly in matrix visual, nor by year

DataInsights — Tue, 08 Apr 2025 21:33:07 GMT

schaezac,

Glad to hear it works! ADDCOLUMNS is a powerful function and an important tool in the toolkit.