topic Using dateadd with Max in DAX Commands and Tips

Using dateadd with Max

Rngomoa — Thu, 02 Mar 2023 10:21:23 GMT

Am trying to create a Dax to select cases within the defined date period as follows. However, when remove the curdate2, am able to view some results but when i use curdate2, there is not result. I however, need to select cases whose visit is above -28 days of the selected date.

tx_curr2 =

VAR curdate = MAX(CalenderDate[dates])

VAR curdate2=DATEADD(CalenderDate[dates],-28,DAY)

RETURN

COUNTX(

FILTER(

ADDCOLUMNS(

SUMMARIZE(Greencard,Greencard[patient_id],Greencard[mflcode],"tx_curr2", CALCULATE(COUNT(Greencard[patient_id]), Greencard[visit_date] <= curdate && Greencard[visit_date] <= Greencard[ExitDate1] && Greencard[visit_date] > curdate2)

"tx_curr3", IF([tx_curr2] > 0, 1, 0)

[tx_curr3] > 0

),Greencard[patient_id])

Re: Using dateadd with Max

andhiii079845 — Thu, 02 Mar 2023 10:35:41 GMT

The probem is that you use a column in this DAX expression:
You need to use something like SELECTEDVALUE(CalenderDate[dates]) or VALUES or MAXX i think.
curdate2=DATEADD(CalenderDate[dates],-28,DAY)

Re: Using dateadd with Max

Rngomoa — Thu, 02 Mar 2023 11:02:01 GMT

Thanks for your response. I have tried your suggestion but it's not working.

Re: Using dateadd with Max

andhiii079845 — Thu, 02 Mar 2023 11:11:51 GMT

Can you please show, how you use the the measure? In a matrix? With which columns?

Re: Using dateadd with Max

andhiii079845 — Thu, 02 Mar 2023 11:56:52 GMT

I play a little bit with your problem. Very interesting, this work, i hope:

-->

tx_curr2 =

VAR curdate = MAX(CalenderDate[dates])

VAR curdate2=SELECTEDVALUE(CalenderDate[dates])-28

RETURN

COUNTX(

FILTER(

ADDCOLUMNS(

"tx_curr3", IF([tx_curr2] > 0, 1, 0)

[tx_curr3] > 0

),Greencard[patient_id])