Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

 

6 REPLIES 6
Phil_Seamark
Microsoft Employee
Microsoft Employee

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 : aka.ms/practicalDAX

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?

Thanks,
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 : aka.ms/practicalDAX

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

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.