Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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.


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

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.


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.
selimovd
Super User
Super User

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
 

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors