Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

DAX Query to count on WHERE condition Date

Hi, I have 3 columns -  Custid, Prodid, Reservation Date and Cancellation Date and below is the requirement.

 

I want to get Count of Custid WHERE Reservation date is in last 4 weeks from todays date WHERE there is no Cancellation date 

DIVIDED by Count of Distinct Prodid DIVIDED by 13

 

Can someone please give me this DAX query ? Many Thanks

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    Try

    CALCULATE(COUNT(custid),FILTER(ALL(table),Reservation date>=TODAY()-28&&Reservation date<=TODAY()&&Cancellation Date=BLANK()))/CALCULATED(DISTINCT(Prodid),ALL(table))/13

    It's better to share some sample data and expected result to us.

     

    Best Reagrds,

    Jay