cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
malcolms Frequent Visitor
Frequent Visitor

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 I cannot filter data based on the Area slicer in the FactDollar Table.

 

To give you more insight to my problem. The Fact Dollar table contains area wise data of my contractual employees and the start and end month they were involved. I'm able to get the information for the amount spent each month for all of them by using the post below but if I wish to break it up by the specific Area I'm unable to do so. Tried various methods but none of them worked.

Kindly Help.

 

 Vacancy.PNGData Model. Date column in Dates table is created by the Calendar Auto Dax Function. and Dollar is is the cumulative sum function in the post link

Vacancy2.PNGwhen I select a particular area in the slicer there is no Impact on the chart below.

https://community.powerbi.com/t5/Desktop/Calculate-value-between-2-dates/m-p/156792#M67923

 

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Sum Dollar value between 2 dates

Hi, I made a simple example with your data and a calculated table In this file.


Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

 

 

Alberto Ferrari - SQLBI

View solution in original post

9 REPLIES 9
v-huizhn-msft Super Contributor
Super Contributor

Re: Sum Dollar value between 2 dates

Hi @malcolms,

When you select Area, there is no impact on the chart. That is because when you create a calculated column Date[Dollar], you get all areas dollars. And the calculated column would not be changed by slicer, you can review this knowledage base for more details.

So for your requirement, you need to get different area's dollars. I create the following sample table named 'Test'.

1.PNG

Then create a date table using the formula.

Date = CALENDAR(MIN(Test[Start Date]),MAX(Test[End Date]))


In the Date table, create calculated column using the formulas.

AA-Dollar = CALCULATE(SUM(Test[Dollar]),
    FILTER(Test,
        Test[Start Date]<='Date'[Date]&&Test[End Date]>='Date'[Date]&&Test[Area]="AA"
    )
)

BB-Dollar = CALCULATE(SUM(Test[Dollar]),
    FILTER(Test,
        Test[Start Date]<='Date'[Date]&&Test[End Date]>='Date'[Date]&&Test[Area]="BB"
    )
)

CC-Dollar = CALCULATE(SUM(Test[Dollar]),
    FILTER(Test,
        Test[Start Date]<='Date'[Date]&&Test[End Date]>='Date'[Date]&&Test[Area]="CC"
    )
)

DD-Dollar = CALCULATE(SUM(Test[Dollar]),
    FILTER(Test,
        Test[Start Date]<='Date'[Date]&&Test[End Date]>='Date'[Date]&&Test[Area]="DD"
    )
)


Please note, I think we should use the sum function(in bold) rather than max in the post. For example, the dollar from 2017/4/1 to 2017/4/10 is 10, another record dollar 2017/4/5 to 2017/4/10 is 20, the total dollar should be (10+20) during 2017/4/5-2017/4/10, rather than the max value 20.

Finally, you need to create a measure to get the corresponding total dollar based on the selected area value. 

Total-dollar = SWITCH(SELECTEDVALUE(Test[Area]),
    "AA",SUM('Date'[AA-Dollar]),
    "BB",SUM('Date'[BB-Dollar]),
    "CC",SUM('Date'[CC-Dollar]),
    "DD",SUM('Date'[DD-Dollar])
)


Create a chart, select the data as x-axis, the measure as value, then you select different in slicer, there is impact on the chart. Please see the screenshot, and you can download the attachment to test.
2.PNGSelect Area "AA"3.pngSelect Area "CC"

Best Regards,
Angelia

malcolms Frequent Visitor
Frequent Visitor

Re: Sum Dollar value between 2 dates

Thanks, This approach is good when I have limited number of values in the area field. But in my case there is possibility of the number of areas increasing in the future and I want the calculation to provision for it automatically. Also if I select no value in the slicer the total field is blank. Can we have an approach where no matter how many values or dimension are being added to the fact table it dynamically give me the sum like it gives me in the below calculation I use to count the number of employees between a time period. Count of Active Vacancies = VAR currentDate = MAX ( 'Dates'[Date] ) RETURN CALCULATE ( COUNTROWS ( Vacancy ), FILTER ( Vacancy, ( Vacancy[Vacancy Start]<= currentDate && Vacancy[Filled] >= currentDate ))
v-huizhn-msft Super Contributor
Super Contributor

Re: Sum Dollar value between 2 dates

Hi @malcolms,

I thought the approch using VAR before, but the current data is dynamic, and it seems to compare the mutiple currents dates to mutiple start/end date. It always returns error message. So that I post the solution above, I will post update if I find another better solution.

Best Regards,
Angelia

Re: Sum Dollar value between 2 dates

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

Alberto Ferrari - SQLBI
malcolms Frequent Visitor
Frequent Visitor

Re: Sum Dollar value between 2 dates

Hi Alberto,

 

Thanks for the reply. Sorry for the delay. I had issues posting my reply.

 

I did use a measure initially and had visited your site for reference.  The problem here is that the numbers do not roll up. The second Image shows the daily cost of the employee. So from 3rd May to 23rd May, I was paying $330. From 24th May to 31st May I was paying 763. In total I paid 330*21 & 763*8= 6930 +6104 = 13034. Instead, if you look at the 1st image you would see it displays $330 for May. I'm ok even if the values cumulates on a daily basis. But would like to see the value 9734 for the month of May.

 

Regards

Malcolm

 

Vacancy4.pngVacancy5.png

malcolms Frequent Visitor
Frequent Visitor

Re: Sum Dollar value between 2 dates

Vacancy7.pngMay at Day levelVacancy8.pngDec at Day levelVacancy6.pngMonth 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
    )
)

Re: Sum Dollar value between 2 dates

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

Alberto Ferrari - SQLBI
malcolms Frequent Visitor
Frequent Visitor

Re: Sum Dollar value between 2 dates

 

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

Re: Sum Dollar value between 2 dates

Hi, I made a simple example with your data and a calculated table In this file.


Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

 

 

Alberto Ferrari - SQLBI

View solution in original post

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 167 members 1,771 guests
Please welcome our newest community members: