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

[bboobe]

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?

[Greg_Deckler]

@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.

[bboobe]

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

.

[Greg_Deckler]

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

[bboobe]

@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. 

[Greg_Deckler]

@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
    )

[bboobe]

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. 

[Greg_Deckler · Accepted solution]

@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
    )

[bboobe]

Thank you VERY MUCH @Greg_Deckler 

[Greg_Deckler]

Sorry it took us some time to get there @bboobe !

[bboobe]

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!