Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I'm using a matrix to display a triangle. The triangle is cumulative and currently extends to the maximum developmentperiodkey.
This is all well and good but I want it to blank /hide/not calculate any values which are greater than todays month (developmentperiodkey Rows) highlighted in RED based on the Year (Columns). but don't have the DAX skills to do it, not for the want of trying...
The code for the measure is currently:
Measure = CALCULATE([Value],FILTER(ALL('DevelopmentPeriods'),DevelopmentPeriods[DevelopmentPeriodKey]<=MAX(DevelopmentPeriods
Any help would be greatly appreiated.
Many thanks.
Solved! Go to Solution.
Try this
Measure = CALCULATE([Value],FILTER(ALL('DevelopmentPeriods'),
DevelopmentPeriods[DevelopmentPeriodKey]<=MAX(DevelopmentPeriods))) * DIVIDE([Value],[Value])
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Try this
Measure = CALCULATE([Value],FILTER(ALL('DevelopmentPeriods'),
DevelopmentPeriods[DevelopmentPeriodKey]<=MAX(DevelopmentPeriods))) * DIVIDE([Value],[Value])
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Incredible... thank you so much for the solution and amazingly quick response!!??
You don't know how long I'm spent looking at this.
Very much obliged.
@AnonymousI'm glad it worked out, I love PowerBI community. Feel free to reach out for any future questions. Cheers!!!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks again for the answer, however since nothing is straight forward I have a slight problem with the solution.
Below is a slightly different example, where the value (In Orange) has not changed in a period so the Cumulative Values stays the same.
These values are now (on the right) understandably blank, is there any way to prevent this from happening until we reach the Current Months Development period?
Many thanks in advance
Please use relative date filter and remove divide calculation with total
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks, tried that, but now values contine to end of development periods as before:
Running Total = CALCULATE([Value],FILTER(ALL('DevelopmentPeriods'),DevelopmentPeriods[DevelopmentPeriodKey]<=MAX(DevelopmentPeriods[DevelopmentPeriodKey])))
Is there a way to restrict what they develop to by a dynamic date.
I believe The ALL() Filter set this to all periods?
Thanks again!
Can you share the screen shot what you put in filter for relative date filter?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
I'm trying to simplify things with the example data.
The Relative filter I've just created uses the Accounting Period Start Date.
This is because there are no dates in the development periods table.
Each Value havs a development period derived from the Accounting period of the transaction:
Thank you
@Anonymoussorry Im little lost here, I guess it will make sense if you can share sample data and how your tables are related.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Completely understand, I'm not making it very clear.
Its quite complex, I'm also using SSAS tab as the data source.
I'll see what I can do to Clarify.
Thanks again.
or you can add a date filer using relative filtering.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
User | Count |
---|---|
85 | |
85 | |
67 | |
61 | |
56 |
User | Count |
---|---|
137 | |
110 | |
92 | |
84 | |
69 |