cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VivekC
Helper II
Helper II

How to get sum of values between two DateTime

Hello ,
I want to calculate sum of revenue between two DateTime using DAX.
For example :-
If I want to calculate sum of revenue between 01/07/2019 4:00:00 AM (dd/mm/yyyy hh:mm:ss ) and 10/07/2019 4:00:00 AM.

Is there any function which can do this calculations.
Any help is highly appreciated.

Thanks in advance

Regards,
VivekC
3 REPLIES 3
Anonymous
Not applicable

Hi, 

 

So here I am elaborating my issue with one example.

 

My requirement is to add all the billing amount in between 01/01/2019 4:00 am to 02/01/2019 4:00 am using DAX.

 

The result should be  $158 .

 

issue.PNG

Anonymous
Not applicable

[Total Billing] =
var __start = DT1 -- DT1 and DT2 are dates taken from somewhere, possibly a slicer
var __end = DT2
var __result =
    calculate(
        sum( T[Billing] ),
        T[Transaction Date] >= DT1,
        T[Transaction Date] <= DT2,
        ALL( T[Transaction Date] )
    )
return
    __result

I'll tell you right away: your model is sub-optimal. If the table you've shown is the fact table and you don't have any dimensions in a one:many relationship with the table, especially Date table and Time table, then you'll very soon be faced with DAX that will not be fast and no amount of fiddling with it will make it so. I'd really suggest you learn about the proper model design before you start building the whole thing WRONGLY. If after having built 80% of the model you'll find that you've done it incorrectly, think about the amount of work that will go into starting almost all over again...

 

You've been warned.

 

Best

Darek

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.