Calculate the month from a date slicer..

[DataAnalyst_99]

Hello All,

I had a requirement that calculated the no. of failed jobs for the month for a selected date. My dashboard at the moment displays day wise data for a particular selected date. What I would like to calculate is for the month of the selected date, how many jobs have failed. Is there a way to do this without putting a month slicer?

For now I have created a measure - calculate(sum(data[statuscounts]), data[status] = "Failed"). This works well if I use a month slicer

Regards

[Jos_Woolley · Accepted solution]

Hi,

Assuming your slicer is from a date table named 'Dates' (which has a relationship with your 'data' table):

MyMeasure = 
VAR MyDate =
    SELECTEDVALUE( Dates[Date] )
RETURN
    CALCULATE(
        SUM( data[statuscounts] ),
        data[status] = "Failed",
        FORMAT( Dates[Date], "mm-yy" ) = FORMAT( MyDate, "mm-yy" )
    )

Regards

[DataAnalyst_99]

Hi @Jos_Woolley 

This works! However, is there a way to modify it using the before/after date selection? since I am using a single date picker

Really appreciate your help! Thank you

[Jos_Woolley]

Not sure what you mean, sorry. Can you clarify?

Regards

[DataAnalyst_99]

@Jos_Woolley  I was able to fix the issue on my end. I did have a question regarding your calculation though - for the month of July I am getting 0 failures till date. However, say there was 1 failure on July 29th. Will the value then show 1 for the month? That is, will it aggregate day on day?

Regards

[Jos_Woolley]

I'm afraid I don't understand your new request either. The measure does precisely what you asked: whatever the month of the entry you choose in the date slicer, it will return the required sum for all entries with that month and year. Or were you wanting perhaps to include months from different years in the same total?

Regards

[DataAnalyst_99]

@Jos_Woolley  Sorry I should have been more clear. I was just wondering how the measure would behave when the data refreshes. This report refreshes daily. For the past months, the measure is working as expected. Just wanted to understand the behaviour once it refreshes and there is a failure for a particular day. For example, for July 2022, there have been 0 failures till date. if the data refreshes tomorrow and there is 1 failure, will the measure now reflect 1 for July?

Regards

[Jos_Woolley]

Yes, the measure will recalculate when your data is refreshed.

Regards