DAX Measure Totals not working

[MarioDiaz]

Hi all,

I have the following table:

projecttable :

I want to build the following matrix that returns the project value for every year until the sales year is passed.

I added the following measure:

AMT=

VAR CurrentYear =  SELECTEDVALUE('Calendar'[Year]) -- Get the current year

VAR SaleYear = CALCULATE( YEAR(SELECTEDVALUE(projecttable [Sale Date])),ALLEXCEPT(projecttable ,projecttable [Project]))  -- Get the sales date year

VAR ProjectValue = CALCULATE(SELECTEDVALUE(projecttable [Project Value]),ALLEXCEPT(projecttable ,projecttable [Project]))  -- Get the project value

var Result = IF(CurrentYear <= SaleYear, ProjectValue, 0)

RETURN Result

I got the visual working except for the totals that are not adding up. Is there a better way to implement this measure and hopefully considering the addition of row values?

Thanks a lot in advance

[Anonymous · Accepted solution]

Hi, @MarioDiaz 

Based on your information, I create a table:

Then create a new table:

Calendar = CALENDAR(MIN('Table'[Sale Date]),MAX('Table'[Sale Date]))

Create a calculated column:

Year = YEAR('Calendar'[Date])

Create a new measure:

AMT = 
VAR CurrentYear = SELECTEDVALUE('Calendar'[Year])
VAR SaleYear = CALCULATE(YEAR(SELECTEDVALUE('Table'[Sale Date])), ALLEXCEPT('Table', 'Table'[Project]))
VAR ProjectValue = CALCULATE(SELECTEDVALUE('Table'[Project Value]), ALLEXCEPT('Table', 'Table'[Project]))
VAR Result = IF(CurrentYear <= SaleYear, ProjectValue, 0)
RETURN
    IF(
        HASONEVALUE('Table'[Project]),
        Result,
        SUMX(
            VALUES('Table'[Project]),
            IF(CurrentYear <= SaleYear, ProjectValue, 0)
        )
    )

Here is my preview:

And I have another test. Enter data Year 2024 to 2028:

Then create another measure AMT2:

AMT2 = 
VAR CurrentYear = SELECTEDVALUE('Calendar 2'[Year])
VAR SaleYear = CALCULATE(YEAR(SELECTEDVALUE('Table'[Sale Date])), ALLEXCEPT('Table', 'Table'[Project]))
VAR ProjectValue = CALCULATE(SELECTEDVALUE('Table'[Project Value]), ALLEXCEPT('Table', 'Table'[Project]))
VAR Result = IF(CurrentYear <= SaleYear, ProjectValue, 0)
RETURN
    IF(
        HASONEVALUE('Table'[Project]),
        Result,
        SUMX(
            VALUES('Table'[Project]),
            IF(CurrentYear <= SaleYear, ProjectValue, 0)
        )
    )

It is my preview:

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

[Greg_Deckler]

@MarioDiaz First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8