Calculate a total between two dates

[maurcoll]

Hi

Is it possible to calculate the total orders received between two dates based on a date column. Data is from excel so all columns are in one table.

I have Offer Start date and Offer End Date and Order Date, each customer will have a different start and end date

I want to know

1. How many orders were received prior to the offer start date
2. How many orders were received between the offer start date and offer end date
3. How many orders have been received between the offer end date and today

So in the below example it would be 1 prior to offer date, 1 during and 1 after offer has finished.

Customer	Order Date	Offer Start Date	Offer End Date	Order Number
A	01/10/2024	06/10/2024	06/11/2024	1
A	07/10/2024	06/10/2024	06/11/2024	2
A	17/11/2024	06/10/2024	06/11/2024	3

Thank you

[Anonymous · Accepted solution]

Hi, @maurcoll 

You can try the following dax to achieve your need.

Before = 
VAR _minDate = CALCULATE(MIN('Table'[Offer Start Date]),ALLEXCEPT('Table','Table'[Customer]))
VAR _counts = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Order Date]<_minDate))
RETURN
_counts

Between = 
VAR _minDate = CALCULATE(MIN('Table'[Offer Start Date]),ALLEXCEPT('Table','Table'[Customer]))
VAR _maxDate = CALCULATE(MAX('Table'[Offer End Date]),ALLEXCEPT('Table','Table'[Customer]))
VAR _counts = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Order Date]>=_minDate && 'Table'[Order Date]<=_maxDate))
RETURN
_counts

After = 
VAR _minDate = CALCULATE(MIN('Table'[Offer Start Date]),ALLEXCEPT('Table','Table'[Customer]))
VAR _maxDate = CALCULATE(MAX('Table'[Offer End Date]),ALLEXCEPT('Table','Table'[Customer]))
VAR _today = TODAY()
VAR _counts = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Order Date]>_maxDate && 'Table'[Order Date]<=_today))
RETURN
_counts

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum