Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
Currently my matrix visual looks like this:
My problem is that i don't know how to hide the "diff" and "%" data of february. Assume we are in april and we are always excluding current month.
I would rather my result to look like this:
Hence, I have a visual level filter: relative date filtering showing items when the value is in the last 2 calendar months.
My measures are pretty simple:
sales = AVERAGEX(table, table[column]) diff = VAR previous_sales = CALCULATE(sales, PREVIOUSMONTH('Date'[Date]) RETURN sales - previous_sales diff % = DIVIDE(sales-previous_sales,previous_sales)
Solved! Go to Solution.
@RogerSteinberg you can try following approach
Add "Month offset" column in your date table
Month Offset = DATEDIFF (TODAY(), DateMaster[DateID], MONTH )
And update your "diff %" measure like this
Diff % = IF( MAX( Date[Month Offset] = 0, <your diff % calculation> )
Basically it is going to check if month offset is not 0 then diff % is blank.
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.
@RogerSteinberg may be you want to change to table visual and use following to break down your dax and use following measures in your visual
- sales
- prev sales
- diff%
I believe it will have cleaner look.
sales = AVERAGEX(table, table[column]) previous_sales = CALCULATE(sales, PREVIOUSMONTH('Date'[Date]) diff = sales - previous_sales diff % = DIVIDE(diff,previous_sales)
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.
Any way to keep the matrix visual ?
@RogerSteinberg you can try following approach
Add "Month offset" column in your date table
Month Offset = DATEDIFF (TODAY(), DateMaster[DateID], MONTH )
And update your "diff %" measure like this
Diff % = IF( MAX( Date[Month Offset] = 0, <your diff % calculation> )
Basically it is going to check if month offset is not 0 then diff % is blank.
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.
@parry2k It worked well however i had a visual level filter which only takes the last two calendar months.
I've modified the diff formula to equal to -1 instead of 0.
My issue now is that all the dates are being shown (two years).
My visual level filter is not taken into account anymore. How do i modify the diff formula to only show the last two calendar months.
@RogerSteinberg sorry not fully sure what you mean, if you can share some image to explain, it will help.
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.
@parry2k Basically I only want to show the data for the past two calendar months. So i added a relative visual level filter.
For example, we are in april. So the matrix will show february and march.
When i added your formula, it added every month since 2016 and the visual level filter doesnt work anymore.
@RogerSteinberg hmmm, can you share how the output looks like? Not sure why it is showing all the months.
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.
@RogerSteinberg interesting, can you double check "Month Offset" is getting calculated correctly. It should be unique for each month. I will double check at my end thou.
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.
@parry2k . Month offset is fine. Its -1 for all of the month of march, -2 for february.
basically i only want to show data when its -1 and -2
@RogerSteinberg I checked and not able to produce the issue at my end, everything is working as expected. Can you share pbix file (may be remove any sensitive information it has). You can also send me PM.
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.
@parry2k It worked well however i had a visual level filter which only takes the last two calendar months.
I've modified the diff formula to equal to -1 instead of 0.
My issue now is that all the dates are being shown (two years).
My visual level filter is not taken into account.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |