Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a slicer with year and month information as yyyymm.
Based on the year-month selection in the slicer, the matrix visual has to display data from chosen month to the start of the year.
Sample Scenario
Slicer Values - 201912 to 201801
Selected Value - 201904
Expected columns in matrix
201904 201903 201902 201901
201904 | 201903 | 201902 | 201901 | |
ABC | a1 | a5 | a6 | a11 |
XYZ | a2 | a20 | a12 | a22 |
DEF | a3 | a31 | a30 | a38 |
Creating a measure using SELECTEDVALUE isnt working as i dont want any aggregation of data.
Please suggest a solution.
Solved! Go to Solution.
@sreekanths assuming this slicer table is not connected with your actual data table. Try the following measure, tweak it as per your need.
Measure =
VAR __maxDate = MAX ( SlicerTable[Date] ) //it will give the max date on the selected year/month
VAR __selectedYear = YEAR ( __maxDate )
RETURN
CALCULATE (
SUM ( DataTable[Column] ),
YEAR ( DataTable[DateColumn] ) = __selectedYear,
DataTable[DateColumn] <= __maxDate
)
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
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.
hi @sreekanths
You just drag year-month field into "Columns" of the matrix visual and then drag it into a number range slicer.
https://docs.microsoft.com/en-us/power-bi/desktop-slicer-numeric-range
https://docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual
Regards,
Lin
@sreekanths assuming this slicer table is not connected with your actual data table. Try the following measure, tweak it as per your need.
Measure =
VAR __maxDate = MAX ( SlicerTable[Date] ) //it will give the max date on the selected year/month
VAR __selectedYear = YEAR ( __maxDate )
RETURN
CALCULATE (
SUM ( DataTable[Column] ),
YEAR ( DataTable[DateColumn] ) = __selectedYear,
DataTable[DateColumn] <= __maxDate
)
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
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 |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |