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
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:
Solved! Go to 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.
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.
@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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
89 | |
84 | |
76 | |
64 |
User | Count |
---|---|
135 | |
112 | |
98 | |
98 | |
92 |