DAX Query to count on DATE WHERE condition

[Anonymous]

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

[Jihwan_Kim · Accepted solution]

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]

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

Blog: WhatTheFact.bi