Forum Discussion

MarioDiaz's avatar
MarioDiaz
Frequent Visitor
2 years ago
Solved

DAX Measure Totals not working

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
General Comment
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies