Forum Discussion

Regular Visitor

9 years ago

Build calculated column to flag Latest month data based on Slicer(s)

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...

Microsoft Employee

9 years ago

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

machbi
Regular Visitor
9 years ago
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 1

Pic 2: However when a slicer selection filters down the months, the latest month visual does not reflect this correctly.Pic 2
- Anonymous
  Not applicable
  9 years ago
  Hi machbi,
  
  What fields do you use to create the 6 slicers? Could you please post sample data of your tables so that we can test?
  
  Thanks,
  Lydia Zhang
  - machbi
    Regular Visitor
    9 years ago
    Thanks Anonymous . I just put together a sample PBIX file. How can I upload/share?