Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
MarioDiaz
Frequent Visitor

DAX Measure Totals not working

Hi all,

 

I have the following table:

projecttable :

MarioDiaz_0-1721225703720.png

 


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

MarioDiaz_1-1721225744175.png

 


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
1 ACCEPTED SOLUTION
v-yohua-msft
Community Support
Community Support

Hi, @MarioDiaz 

Based on your information, I create a table:

vyohuamsft_0-1722585893744.png

Then create a new table:

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

vyohuamsft_1-1722585929424.png

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:

vyohuamsft_2-1722586039463.png

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

vyohuamsft_3-1722586235940.png

 

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:

vyohuamsft_4-1722586341620.png

 

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.

View solution in original post

2 REPLIES 2
v-yohua-msft
Community Support
Community Support

Hi, @MarioDiaz 

Based on your information, I create a table:

vyohuamsft_0-1722585893744.png

Then create a new table:

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

vyohuamsft_1-1722585929424.png

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:

vyohuamsft_2-1722586039463.png

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

vyohuamsft_3-1722586235940.png

 

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:

vyohuamsft_4-1722586341620.png

 

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
Super User
Super User

@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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.