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

[machbi]

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.

Thanks much!!

[Phil_Seamark]

Hi @machbi,

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]

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

[v-yuezhe-msft]

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]

Thanks @v-yuezhe-msft .  I just put together a sample PBIX file.  How can I upload/share?

[Phil_Seamark]

Hi @machbi,

If you upload to your preferred cloud file storage tool (eg One Drive, Dropbox etc), you can often "get a link" to share a read only copy of that file that we can use to download and use to help you with.

[machbi]

Here's the link to download the sample PBIX file.  Thank you all for your help.

Power BI Sample File

Thanks for the tip @Phil_Seamark.  Was originally looking for an internal PBI community attachment option, but this works as well.