cancel
Showing results for 
Search instead for 
Did you mean: 
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
Microsoft

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.