Forum Discussion

maurcoll's avatar
maurcoll
Helper IV
1 year ago
Solved

Calculate a total between two dates

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.

CustomerOrder DateOffer Start DateOffer End DateOrder Number
A01/10/202406/10/202406/11/20241
A07/10/202406/10/202406/11/20242
A17/11/202406/10/202406/11/20243


Thank you

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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