Forum Discussion

Rngomoa's avatar
Rngomoa
Frequent Visitor
3 years ago
Solved

Using dateadd with Max

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])

 

  • andhiii079845's avatar
    andhiii079845
    3 years ago

    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)

4 Replies

  • andhiii079845's avatar
    andhiii079845
    Solution Sage
    3 years ago

    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)

    • Rngomoa's avatar
      Rngomoa
      Frequent Visitor
      3 years ago

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

       

  • andhiii079845's avatar
    andhiii079845
    Solution Sage
    3 years ago

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

  • andhiii079845's avatar
    andhiii079845
    Solution Sage
    3 years ago

    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(
    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])