DAX Formula for Counting Column Values from Previous Day Returns Blank Output

[cgarg]

Hi Team,

Consider the below table abc:

status              dateend (mm/dd/yyyy)

Success           01/01/2023

Success           01/02/2023

Success           01/02/2023

Failure             01/08/2023

A calendar table was built using abc table. It also had same date format (mm/dd/yyyy)

1:* relationship is established from calendar to abc table with single cross filter direction.

Date slicer is built using calendar table.

Now, when a date is selected from slicer, I want to display count of 'success' status for the date before the selected date.

For example, when we chose 01/02/2023 from slicer, we should get count of 'success' status as 1 because we have only 1 success on 01/01/2023. 

To calculate previous date, dax was written as: 

                                                                        dax_prevDay = PREVIOUSDAY(CalendarTable[Date])

dax_prevDay is also in mm/dd/yyyy format & is giving expected result

the dax which will do the count of 'success' status is:

                                                                                   dax_test = CALCULATE(COUNT(abc[status]),                                                                                                                                     FILTER(abc,abc[status]="Success"),

                                                                                   FILTER(abc,abc[dateend]=[dax_prevDay]))

But, the above dax is giving Blank as result even if there is data for previous date.

Note:

1. It seems the problem is with 2nd filter condition.
2. When I hard core date in filter cond: FILTER(abc,abc[dateend]=DATE(2023,1,1)), I'm getting correct output. The doubt here is even [dax_prevDay] is in Date type
3. I even explicitly converted [dax_prevDay] to date type assuming if by any chance [dax_prevDay] is in text by enclosing this dax inside DATEVALUE function. But, still the issue is not resolved

[pcoley]

@cgarg 
Please try with this measure:

CountSuccessPD =
CALCULATE (
    COUNTROWS ( abc ),
    PREVIOUSDAY (
        ALLSELECTED ( CalendarTable[Date] )
    ),
    abc[status]="Success"
)

I hope this helps, if so please accept as a solution. Kudos are welcome😀

[Anonymous]

Thanks for the reply from Kedar_Pande.

Hi @cgarg ,

Create a slicer to use the field "Date" of a new table.

Then create two new measures:

judgement = IF(MAX('abc'[dateend])=[dax_prevDay]&&MAX('abc'[status ])="Success",1,0)

count = SUMX(VALUES('abc'[dateend]),[judgement])

Result:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[cgarg · Accepted solution]

Hi  ,Thank you for your response. This doesn't workout for me either (probably because the dataset I've is huge having several columns & multiple values even for 'status' column & hence conditions are failing). However, below is the dax formula that worked for me:
 

test =

CALCULATE(

   COUNT(abc[status]),

    CalendarTable[Date] = MAX(CalendarTable[Date]-1)

   )

I've explicitly set the status filter to Success from filters pane.

I'd like to thank the community for taking pro-active efforts in resolving the issue. Thank you once again!

@Anonymous

[Anonymous]

Hi @cgarg ,

It seems that you have gotten a solution. Could you please mark your reply as solution? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you very much for your kind cooperation!

Best Regards,
Zhu

[Kedar_Pande]

@cgarg 

You can try:

dax_test = 
CALCULATE(
COUNT(abc[status]),
abc[status] = "Success",
FILTER(
ALL(abc),
abc[dateend] = [dax_prevDay]
)
)

Ensure that your CalendarTable[Date] is properly set up and all relationships are correctly established in your model.

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[cgarg]

@Kedar_Pande 
Thank you for your quick response. Tried your suggested formula but didn't work out for me

[Kedar_Pande]

@cgarg 

Even though dax_prevDay is a Date type, it's good practice to ensure it's returned as a valid Date type in the filter condition.

[cgarg]

@Kedar_Pande 
I believe that the Date type returned is valid because when I checked the output of dax_prevDay by populating the card visual, I was getting correct result