SELECTED Value from slicer in not filtering previous month results

Question

I have a dataset like below and I joined it with a calendar table on a created date.

I am trying to do an avg daily cases MoM analysis, I am comparing the current month vs. the Previous month's avg daily cases.

For example, IF I got 60 distinct cases in May, and my daily avg cases for May is 2 cases per day. I want to calculate the same for the previous month when I select Month from the filter.

Here I selected May-2021. I want my measure to calculate Apr-2021 Avg Daily cases.

So I created two measures, one is for the selected month, and another one is for prior avg daily cases.

Avg Daily Cases =
VAR Selected_Month =
SELECTEDVALUE ( 'Date'[MMM-YYYY] )
VAR Month_Days =
CALCULATETABLE (
VALUES ( 'Date'[DateAsInteger] ),
FILTER ( 'Date', 'Date'[MMM-YYYY] = Selected_Month )
)
RETURN
CALCULATE (
AVERAGEX ( Month_Days, CALCULATE ( DISTINCTCOUNT ( MyTable[CaseNo] ) ) )
)

Prior Month Avg Daily Cases

Avg Daily Cases Prev Month =
VAR Selected_Month =
SELECTEDVALUE ( 'Date'[MMM-YYYY] )
VAR Last_Month_Days =
CALCULATETABLE (
VALUES ( 'Date'[DateAsInteger] ),
FILTER (
'Date',
MONTH ( 'Date'[Date] )
= MONTH ( Selected_Month ) - 1
&& 'Date'[Year] = YEAR ( Selected_Month )
)
)
RETURN
CALCULATE (
AVERAGEX ( Last_Month_Days, CALCULATE ( DISTINCTCOUNT ( MyTable[CaseNo] ) ) )
)

Avg Daily Cases measure working fine and getting the expected results, however, Avg Daily Cases Prev Month is not working.

What do you mean that "Avg Daily Cases Prev" is not working? Do you get an error? Output is incorrect? Try to debug your variable Last_Month_Days (for example add new Table from your code and check what you get). — msta42a
@msta42a the problem was the filter context for the previous month. Because you filter on May 2021, the previous month was not able to get the data from the prior month. — Rahul
then change FILTER ('Date' to: FILTER ( ALL('Date'). ALL is here keyword to remove any context filter from DATE — msta42a

Kin Siang Kin Siang · Accepted Answer · 2021-06-07T02:43:31

Suppose you have total case of 30 in May and 60 in June, and you have the following dataset in order to return comparison for two using single selection and accept the answer if helping :)

First I have create the following table for selection

Current period average case

Current daily case = 
var caseTotal = CALCULATE(DISTINCTCOUNT(Sheet1[Case]),
                    FILTER(Sheet1,Sheet1[Period] in {SELECTEDVALUE(Selection[Selection])}))
return
caseTotal / DAY(EOMONTH(SELECTEDVALUE(Selection[Selection]),0))

Prior comparison

Prior daily case = 
var caseTotal = CALCULATE(DISTINCTCOUNT(Sheet1[Case]),
                    FILTER(Sheet1,Sheet1[Period] in {FORMAT(EDATE(SELECTEDVALUE(Selection[Selection]),-1),"mmm-yyyy")}))
return
caseTotal / DAY(EOMONTH(EDATE(SELECTEDVALUE(Selection[Selection]),-1),0))

Result:

SELECTED Value from slicer in not filtering previous month results

1 Answers