Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX Query to count on DATE WHERE condition

Hi, I have 3 columns - Custid, 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


Can someone please give me this DAX query ?
Many Thanks

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Anonymous 

    Please try to use the below.

    if it is not working, please kindly share a sample data then I can look into it to come up with more accurate measure.

    I am using a custom-date-table that contains the week-offset-number column. It is very useful when calculating week-related things.

     

    Customerscount =
    VAR last4weeks = SELECTEDVALUE(dates[WeekOffset]) -4
    RETURN
    CALCULATE( COUNTROWS( Customers), FILTER( ALLSELECTED(dates), dates[WeekOffset] >= last4weeks && dates[WeekOffset] <= MAX(dates[WeekOffset])), FILTER(Customers, ISBLANK(Customers[Cancellation Date])))
     

    Jihwan Kim

    If this post helps, then please consider accept it as the solution to help the other members find it more quickly.

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, Anonymous 

    Please try to use the below.

    if it is not working, please kindly share a sample data then I can look into it to come up with more accurate measure.

    I am using a custom-date-table that contains the week-offset-number column. It is very useful when calculating week-related things.

     

    Customerscount =
    VAR last4weeks = SELECTEDVALUE(dates[WeekOffset]) -4
    RETURN
    CALCULATE( COUNTROWS( Customers), FILTER( ALLSELECTED(dates), dates[WeekOffset] >= last4weeks && dates[WeekOffset] <= MAX(dates[WeekOffset])), FILTER(Customers, ISBLANK(Customers[Cancellation Date])))
     

    Jihwan Kim

    If this post helps, then please consider accept it as the solution to help the other members find it more quickly.

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hello Anonymous ,

     

    you can achieve that with the CALCULATE function:

    Count Custid =
CALCULATE(
    COUNT( myTable[Custid] ),
    myDate[Cancellation] <> BLANK(),
    DATEDIFF( myDate[Reservation date], TODAY(), DAY ) <= 28
)

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis