topic Re: DAX for Custom Filter to Select Past Months & Current Month's Latest Date in DAX Commands and Tips

DAX for Custom Filter to Select Past Months & Current Month's Latest Date

bboobe — Sun, 24 May 2020 09:37:01 GMT

https://drive.google.com/file/d/1ZBTilbeJxPISCZsqlv3FH2Qffvz09k1a/view?usp=drivesdk
This is a sample of my dataset.

When data is refreshed today (May 24), data will be available until the previous day (May 23).

for the current month, data will be updated incrementally (day wise status). for example - for all departments, I have day wise status. (May 1, May 2, May 3, May 4..... May 31 )

At the end of the current month - May, day-wise status is deleted and only May 31 status (final for the month) is stored and the day wise status is started for June.

I want to show Past months & Current Month -latest date status for monthly trend visual. Currently, I have put "Latest data" in Filters and Selecting past months and "Latest data".

Issue - At the beginning of Next Month, the Filter does not include "May data" and I have to be select manually.

How to achieve automating this selection so that all past months are selected automatically along with Current Month Latest date?

Re: DAX for Custom Filter to Select Past Months & Current Month's Latest Date

Greg_Deckler — Sun, 24 May 2020 09:56:18 GMT

bboobe You want what I refer to as a Complex Selector. The concept is based upon the Inverse Selector. https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Selector/m-p/648290#M325

The concept is that you create a measure that returns 1 or 0 based upon whatever logical criteria makes sense in your situation. So, you would likely create a measure like this:

Selection Measure = IF(MONTH(MAX([Month])) = MONTH(TODAY()) && YEAR(MAX([Month])) = YEAR(TODAY()),0,1)

Then you can just filter for 1 or 0 depending on what you want. Updated PBIX is attached.

Re: DAX for Custom Filter to Select Past Months & Current Month's Latest Date

bboobe — Sun, 24 May 2020 10:57:37 GMT

Hi Greg_Deckler , Thanks for looking into it. But in my case, I would require both past months & Current Month (max date data) to be in display. Like the below screenshot

Re: DAX for Custom Filter to Select Past Months & Current Month's Latest Date

Greg_Deckler — Sun, 24 May 2020 11:17:27 GMT

bboobe so then don't use any filter at all?

Re: DAX for Custom Filter to Select Past Months & Current Month's Latest Date

bboobe — Sun, 24 May 2020 11:49:23 GMT

Greg_Deckler For Past Months, I have one time (last day of month) data and For Current Month, I have day-wise data (May 1, May 2.... May 23) and i want to show latest data for current month.

Re: DAX for Custom Filter to Select Past Months & Current Month's Latest Date

Greg_Deckler — Sun, 24 May 2020 15:16:10 GMT

bboobe If I am understanding correctly then see below. Updated PBIX attached.

Measure 2 = VAR __Measure = DIVIDE(DISTINCTCOUNT('sample'[Dept]),CALCULATE(DISTINCTCOUNT('sample'[Dept]),ALL('sample'[Status]))) VAR __MaxDateCurrentMonth = MAXX(FILTER(ALL('sample'),MONTH([Month]) = MONTH(TODAY()) && YEAR([Month]) = YEAR(TODAY())),[Month]) VAR __Current = MAXX(FILTER('sample',[Month] = __MaxDateCurrentMonth),[Latest Data]) RETURN IF(MONTH(MAX([Month])) = MONTH(TODAY()) && YEAR(MAX([Month])) = YEAR(TODAY()), IF(MAX([Month]) = __MaxDateCurrentMonth,__Measure,BLANK()), __Measure )

Re: DAX for Custom Filter to Select Past Months & Current Month's Latest Date

bboobe — Tue, 26 May 2020 06:25:01 GMT

Thank you Greg_Deckler ! But I need this as a measure that can be applied in Filter.. as I have other visuals (table view for reports) that needs to be applied with this time filter.

Re: DAX for Custom Filter to Select Past Months & Current Month's Latest Date

Greg_Deckler — Tue, 26 May 2020 13:36:29 GMT

bboobe That should be something like:

Measure 3 = VAR __Month = MAX('sample'[Month]) VAR __MaxDateCurrentMonth = MAXX(FILTER(ALL('sample'),MONTH([Month]) = MONTH(TODAY()) && YEAR([Month]) = YEAR(TODAY())),[Month]) VAR __IsCurrentMonth = IF(MONTH(__Month) = MONTH(TODAY()) && YEAR(__Month) = YEAR(TODAY()),TRUE(),FALSE()) RETURN IF( __IsCurrentMonth, IF(__Month = __MaxDateCurrentMonth,1,0), 1 )

Re: DAX for Custom Filter to Select Past Months & Current Month's Latest Date

bboobe — Tue, 26 May 2020 14:29:22 GMT

Thank you VERY MUCH Greg_Deckler

Re: DAX for Custom Filter to Select Past Months & Current Month's Latest Date

Greg_Deckler — Tue, 26 May 2020 14:33:15 GMT

Sorry it took us some time to get there bboobe !

Re: DAX for Custom Filter to Select Past Months & Current Month's Latest Date

bboobe — Tue, 26 May 2020 14:38:54 GMT

Yeah Greg_Deckler , I am glad this is solved 🙂 and I appreciate your patience on solving this 🙂 Thank you for your time, you're awesome!