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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors