Measure for last available date ignoring some filters

[acaitr]

Hello,
I'm trying to get the last values of a table, within the selected month.

* I have a slicer linked to a "Calendar" table with a column "DateOnly" (type Date). It allows the selection of an entire month (ex : May 2021)

* My table is defined like this :

DateOnly	Severity	Status	NumberOfAlerts	Source
01/06/2021	Low	InProgress	3	MDO
01/06/2021	Medium	Resolved	9	MCAS
31/05/2021	Informational	New	7	MDO
31/05/2021	Medium	New	10	MDO
28/05/2021	Medium	Resolved	2	MCAS

* I have two other slicers, linked to a dimension table issued from the two "Severity" and "Status" columns (e.g. it allows filtering on Severity={High, Medium, Informational} and Status={InProgress, Resolved, New}).

* I want to set 3 visuals on which the Severity and Status filters may apply thanks to the slicers :

** one displays the total number of alerts

** one displays the total number of alerts for the MCAS source

** one displays the total number of alerts for the MDO source

I want to display these results for the last available date in the table, and when I say "last available date", it means "ignoring the other filters to define what the last date is" : e.g. in the example above, if my Severity and Stats slicers are set on "Medium" and "Resolved" for May 2021, I want to display "No data" because the last available data in the table is 31/05/2021 but there is no data for the "Medium" and "Resolved" combination.

I plan to define a measure that gives the result for the first result, and to use it on the three visuals : on the last two visuals, I will add a "visual filter" to filter on the source. 1 measure, 3 different visuals thanks to "visual filters".

Problem : I can find the DAX syntax to select the last available date.

For now, if I try this :

FilteredAlerts = 
VAR MaxDate = MAX(M365Alerts[DateOnly])
RETURN
    IF(MaxDate=BLANK(),
        "No data",
        SUMX(
            FILTER(M365Alerts, M365Alerts[DateOnly]=MaxDate),
            [NumberOfAlerts]
        )
    )

It works only when my "Severity" and "Status" slicers don't filter anything.

But when I select the "Medium" and "Resolved" combination (the Date slicer selects "May 2021"), e.g. it selects the intersection of "Severity", "Status" and "Date", e.g. the results for 28/05/2021.

What I want is a "No data" result for this combination (because the last available date is 31/05/2021, and not 28/05/2021).

I tried things to define the MaxDate with ALLEXCEPT, or REMOVEFILTERS or... I also tried to filter or to remove filters in the SUMX parameters... But I don't get the result I want.

Please, help ?

Thanks.

[acaitr · Accepted solution]

Hello,

For the records, here is the solution to select the latest available value :

_LatestAlerts = 
VAR MaxDate_FiltersApplied = MAX('M365Alerts'[DateOnly])
VAR MaxDate_AvailableInTable = CALCULATE(
    MAX('M365Alerts'[DateOnly]),
    FILTER(
        ALL(M365Alerts),
        AND(MONTH('M365Alerts'[DateOnly]) = MONTH(SELECTEDVALUE('Calendar'[YearAndMonth])), YEAR('M365Alerts'[DateOnly]) = YEAR(SELECTEDVALUE('Calendar'[YearAndMonth])))
    )
)
RETURN IF(
	MaxDate_AvailableInTable<>MaxDate_FiltersApplied,
	"No data",
	CALCULATE(
		SUM(M365Alerts[TotalNumberOfActiveUsers]),
		FILTER(M365Alerts, M365Alerts[DateOnly]=MaxDate_AvailableInTable)
	)
)

For the graph displaying 14 average values, despite the Calendar slicer, here is how I managed : using bookmarks and two buttons "Yearly" and "Monthly" :

• when Monthly is selected, the Calendar slicer and the Monthly line graph are displayed. On the graph, I use directly the columns from my table.
• when Yearly is selected, the Calendar slicer is hidden and the Yearly Bar chart is displayed. The chart is configured with a "Date" field on the axis, with only Year and Month selected ; I drill down with the button "Go to the next level in the hierarchy" (double-down-arrow). Selecting the Calendar slicer, I go to the "Format" menu, then "Edit interactions", and I select "None" for the bar chart ; with this solution, I ignore the Date filter in order to display the whole 14 months without creating another DAX measure.

Have fun (or not if, like me, you spent too much time to find a solution... 😄 ) !!!

Regards.

[acaitr]

Hello,

Also, another question linked to the first one : on a page with a graph displaying all values for the current month, I also want to display another graph displaying the average number of data by month for the last 14 months. In other words : a graph with 14 points which are the average number of data for each months.
Problem : the slicer for the selection of the month for the first graph also applies on the second graph. Then, I guess I have to define a measure that calculates the average value for each month, ignoring the current slicer.

How do I ignore this filter ?

Thanks !

[wdx223_Daniel]

VAR _MaxDate=CALCULATE(MAX(M365Alerts[DateOnly]),ALL(M365Alerts)) VAR _ttl=CALCULATE([NumberOfAlerts],M365Alerts[DateOnly]=_MaxDate) RETURN IF(_ttl,_ttl,"No Data")

[acaitr]

Hello wdx223_Daniel,

Thanks for your answer but it doesn't work :

* the formula seems to have a syntax error I corrected this way :

VAR _ttl=CALCULATE(SUM(M365Alerts[NumberOfAlerts]), M365Alerts[DateOnly]=_MaxDate)

* it still returns the 01/06/2021 instead of 31/05/2021.

The result I need has to stay in the selected day (may 2021), but the last available day for this month, whatever are other filters.

I tried something like this :

DEBUG_MaxDate =
VAR _MaxDate=CALCULATE(MAX(M365Alerts[DateOnly]), ALLEXCEPT(M365Alerts, M365Alerts[DateOnly]))
VAR _ttl=CALCULATE(SUM(M365Alerts[NumberOfAlerts]), M365Alerts[DateOnly]=_MaxDate)
RETURN IF(_ttl,_ttl,"No Data")

 But using ALLEXCEPT still returns 01/06/2021.

Trying replacing ALLEXCEPT by ALLSELECTED leads to 28/05/2021 :

VAR _MaxDate=CALCULATE(MAX(M365Alerts[DateOnly]), ALLSELECTED(M365Alerts[DateOnly]))

I have the intuition that I need to use one or a combination of these functions (ALL, ALLEXCEPT, KEEPFILTERS...) but I can't manage to manipulate them properly.

Do you have any idea ?

Thanks.