topic Re: DAX Formula for Counting Column Values from Previous Day Returns Blank Output in DAX Commands and Tips

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

cgarg — Mon, 23 Dec 2024 10:12:59 GMT

Hi Team,

Consider the below table abc:

status dateend (mm/dd/yyyy)

Success 01/01/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:

It seems the problem is with 2nd filter condition.
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
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

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

Kedar_Pande — Mon, 23 Dec 2024 10:18:38 GMT

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

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

cgarg — Mon, 23 Dec 2024 10:39:52 GMT

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

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

Kedar_Pande — Mon, 23 Dec 2024 10:42:01 GMT

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.

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

cgarg — Mon, 23 Dec 2024 10:47:00 GMT

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

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

Anonymous — Fri, 27 Dec 2024 08:43:09 GMT

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.

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

cgarg — Fri, 27 Dec 2024 11:58:14 GMT

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

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

pcoley — Sat, 28 Dec 2024 18:48:23 GMT

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😀

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

Anonymous — Mon, 30 Dec 2024 01:33:10 GMT

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