SELECTED Value is not filtering Previous Avg Daily Cases

[Sharma0815]

Hi All,

I am working on a measure to calculate my avg daily previous month cases whenever I select Month and Year(MMM-YYYY) from the slicer.

For example:

If I select the May-2021 slicer,my measure should calculate the previous month's avg daily cases. but it's giving a blank result.


Here is the DAX I tried.

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 ( 'Table1'[CaseNo] ) )

)

)

Thanks in Advance

[selimovd]

Hey @Sharma0815 ,

I would solve that with the DATEADD function:

DATEADD(
    'Date'[Date],
    -1,
    MONTH
)

I'm not really sure if your last calculation makes sense. If you can show me some data or better share the file, I can help you to get the desired result.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Sharma0815]

Thanks, @selimovd,

I've case data and I joined with the calendar table.

I am doing Avg Daily Cases MoM analysis  I am comparing the current month vs. the Previous month's avg daily cases.

 For example, 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.

I hope my question is clear.

(60/30)

[selimovd]

Hey @Sharma0815 ,

we're getting closer.

Then I would first calculate the average cases by day with SUMMARIZE and ADDCOLUMNS. Then you can do an average of that:

Average Cases =
VAR vSumTable =
    ADDCOLUMNS (
        SUMMARIZE ( myTable, myTable[Date] ),
        "@AmountCases", CALCULATE ( COUNTROWS ( myTable ) )
    )
RETURN
    AVERAGEX ( vSumTable, [@AmountCases] )

Then you can add the measure for the previous month:

Average Cases Last Month = CALCULATE ( [Average Cases], DATEADD ( 'Date'[Date], -1, MONTH ) )

Be aware that you need a proper date dimension for the time intelligence functions to work.

Here you can find my example file:

https://www.swisstransfer.com/d/fb783602-6d29-4230-bb71-a713b7828498

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Sharma0815]

Thanks @selimovd  ,

My DAX slightly different  and I am trying to get  Avg Daily Cases for the month selected in the slicer and also prior month 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] ) ) )
)

Avg Daily Cases Measure is giving expected result (for the selected month in the slicer, however Avg Daily Cases Prev Month is not filtering 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] ) ) )
)

[selimovd]

Hey @Sharma0815 ,

OK, I see that your approach was different, but I cannot help you without more information. Can you share your Power BI file with me? Or can you apply my approach? 

Best regards

Denis

[Sharma0815]

Hi @selimovd ,

Here is my .pbix file, please let me know if you can't access it

https://1drv.ms/u/s!AvfP3aj5GalHjFCnYtsZDGjHcdEu?e=h1ePUV

[selimovd · Accepted solution]

Hey @Sharma0815 ,

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 April. An ALL on the date table would solve that. These two measures should do it:

Avg Daily Cases NEW = 
VAR vSumTable =
    ADDCOLUMNS (
        SUMMARIZE ( ALL('Dept Cases'), 'Dept Cases'[Created Date] ),
        "@AmountCases", CALCULATE ( COUNTROWS ( 'Dept Cases' ) )
    )
RETURN
    AVERAGEX ( vSumTable, [@AmountCases] )

And for the previous month:

Avg Daily Cases Prev NEW = CALCULATE ( [Avg Daily Cases NEW], DATEADD( 'Date'[Date], -1, MONTH ), ALL( 'Date' ) )

The result then looks like this:

My solution file you can find here:

https://www.swisstransfer.com/d/895a47d9-dd1e-4008-946f-2a27b305ada8

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Sharma0815]

Thanks a lot, @selimovd, This is solved my problem.