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

[schaezac]

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!

[DataInsights]

@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

[schaezac]

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

[DataInsights · Accepted solution]

@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

[schaezac]

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

[DataInsights]

@schaezac,

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