Hi Power BI Community,
Hoping I can get some direction on this roadblock I'm facing on a report. On my report, I have a bar chart that shows 12 months of data along the X axis. There are also 6 slicers on the report. I'm trying to add a Multi-row card that only shows the latest month's data. I'm trying to use a visual filter on the multi-row card to filter for just the latest month. The problem is the latest month with data changes based on the Slicers values. For instance the report is filtered on the last 12 months to start. So the bar chart would show Mar '16 to Feb '17. However if a slicer value is selected that filters down the data to only show Jul'16 to Dec '16, the latest month with data would be Dec '16. I would like the multi-row card to show data for only Dec '16.
Here is what I've attempted:
Measure : MaxMonth = CALCULATE(max(Originations[MONTH]),ALLSELECTED(Originations)) //this works great and shows the last date with data based on the selected slicer values
Column: Is Latest Month = if(Originations[MONTH]=Originations[MaxMonth],1,0) //This does NOT work, since I believe calculated columns do not take slicers into consideration.
I tried to create [Is Latest Month] as a measure, but measures cannot be used as filters.
Please help!! I'm pulling my hair out.
Do you have a Date/Calendar table? If so, just add this column to it
Months from today = IFERROR(DATEDIFF('Dates'[Date],TODAY(),MONTH),-1)
You can then use this column/field in your Report, Page and Visual filters where the value = 0
You can also use it for dynamic rolling date ranges
Thank you @Phil_Seamark for the prompt reply! I didn't describe my issue properly. Your solution works great if I needed simply the latest month regardless of how the Slicers affect the data. I'm uploading pics for a better description of my issue. I'm trying to get the Latest Month column (multi-row card visual) to reflect the latest month only when there is data. The latest month with data can change, see pic 2, if I select any of the slicers. For example in Pic 2 when I select Slicer 3 with value 2 then my latest month with data is Sep '16. But my Latest Month visual will not reflect that properly. I'm trying to build a column I can use to dynamically change the latest month based on what is selected by the slicers.
Pic 1: When all months have data then the latest month visual works great.
Pic 2: However when a slicer selection filters down the months, the latest month visual does not reflect this correctly.
What fields do you use to create the 6 slicers? Could you please post sample data of your tables so that we can test?
Get a sneak peek into this year's Power Platform Conference Keynote.
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!