Forum Discussion

malcolms's avatar
malcolms
Frequent Visitor
9 years ago
Solved

Sum Dollar value between 2 dates

Hi,   I wanted to sum the dollar values between 2 dates. The print shot of my data model is attached. I could achieve part of this by implementing the method used in the below post. the problem is ...
Need Help
AlbertoFerrari's avatar
AlbertoFerrari
Most Valuable Professional
8 years ago

Hi Malcolm,

 

It looks like you are using a calculated column where you could easily use a measure, it is a very common mistake, if you are coming from an Excel background, where everything is a column :)

 

If your dataset is a small one (few millions rows), then you can author a measure like this one:

 

Dollars = 
VAR CurrentStartDate = MIN ( 'Date'[Date] )
VAR CurrentEndDate = MAX ( 'Date'[Date] )
RETURN
CALCULATE ( 
	SUM ( Test[Dollar] ),
	Test[Start Date] <= CurrentStartDate,
	Test[End Date] >= CurrentEndDate
)

 

Being a measure, it is completely dynamic and will obey any filter you place in the visuals. If, on the other hand, you need to work on larger models, then I suggest you to take a look at this article I have written some time ago: https://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/.

 

Moreover, I wrote a ton of these common calculations in the time intelligence section of this book, which I guess you might find interesting: https://www.sqlbi.com/books/analyzing-data-with-microsoft-power-bi-and-power-pivot-for-excel/.

 

Please let me know if this solves your scenario.


Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

malcolms's avatar
malcolms
Frequent Visitor
8 years ago

May at Day levelDec at Day levelMonth Level missing May and Dec

Hi Alberto,

 

I came up with the below formula. Issue is that it skips the May 2017 and the December 2017 month when we roll up to the month level. Please refer images at the top.

 

 

Count of Dolars Saved = 
VAR CurrentStartDate = MIN ('Dates'[Date].[Date])
VAR CurrentEndDate = MAX('Dates'[Date].[Date])
RETURN CALCULATE (
    SUMX(Vacancy,Vacancy[Total Dollars]*DATEDIFF(Vacancy[Vacancy Start].[Date],CurrentStartDate,DAY)),
    FILTER (
        Vacancy,
        Vacancy[Vacancy Start].[Date]  <= CurrentStartDate
            && Vacancy[Filled].[Date]  >= CurrentEndDate
    )
)
  • AlbertoFerrari's avatar
    AlbertoFerrari
    Most Valuable Professional
    8 years ago

    Well, at this point I would need to look at the data to understand how you shaped it.

    If you have different values for the daily payment, then you will need more rows, at least one for each daily payment. Nevertheless, the best performance and the easiest formula will be if you use M code to change the model to a daily one.

    Instead of storing (from, to, dailypayment), increase the number of rows so to have one row for each day when you made the payment, with the amount paid. It can be done pretty easily by using some transformation during ETL and, at that point, your formula becomes a very simple SUM.

    I could do it easily, but I would need the model, and you can find a complete solution in that sense in the modeling book, as it is a very common data model. I guess I did it also in the article I linked in an earlier post.


    Have fun with DAX!

    Alberto Ferrari
    http://www.sqlbi.com

    • malcolms's avatar
      malcolms
      Frequent Visitor
      8 years ago

       

      Hi Alberto,

       

      Please see attached link to the data model. Let me know if you have any issues opening it.

       

      Regards

      Malcolm

       

      Drop Box Link