topic Re: How to get sum of values between two DateTime in DAX Commands and Tips

How to get sum of values between two DateTime

Anonymous — Sat, 13 Jul 2019 16:35:15 GMT

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

Re: How to get sum of values between two DateTime

Anonymous — Sat, 13 Jul 2019 16:38:32 GMT

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best

Re: How to get sum of values between two DateTime

Anonymous — Tue, 16 Jul 2019 17:15:34 GMT

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 .

Re: How to get sum of values between two DateTime

Anonymous — Tue, 16 Jul 2019 23:18:22 GMT

[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