Showing results for 
Search instead for 
Did you mean: 
Regular Visitor

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



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

To learn more about DAX visit :

Proud to be a Datanaut!

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 1Pic 1


Pic 2: However when a slicer selection filters down the months, the latest month visual does not reflect this correctly.Pic 2Pic 2

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?

Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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



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.



To learn more about DAX visit :

Proud to be a Datanaut!

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.

Helpful resources

Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

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!

Top Solution Authors