Forum Discussion
Cumulative Sales in Two Date Ranges
I have a Calendar Date Table that has the heirarchy in the first three columns. Followed by my 'Opportunity Product' Table that produces the sales. I then have the delivered date in the same table, followed by the Day of Year that is descending (Jan 1st = 366, Dec 31st = 1). I will always be selecting at least two years for my line graph.
I would like to have a cumulative and continuous line chart that records $0 for any day between the first date-column and the closing date. You can see what the sum of total sales looks like in the attached graph. I have tried the quick measure formula as well as
Hey Anon2020 ,
I am not sure if I understood your problem correctly but could you try adding a zero in the measure formula like follows:
dayRunSum = CALCULATE( SUM('Opportunity Product'[TotalPrice]), FILTER( ALLSELECTED('Calendar'), [Year] = MIN('Calendar'[Year]) && [posDayNoOfYr] <= MIN('Calendar'[posDayNoOfYr]))) + 0I am assuming that the value that do show up (in the current screenshot) are correct.
And make sure you have posDayNoOfYr from Calendar Table in the X-axis.
Here we are overriding the Power BI's default feature of returning blanks (when there is no data) by explicitly doing an addition with 0.
Hope it helps and if does not we can break down the requirement further!
5 Replies
Hey Anon2020 ,
I am not sure if I understood your problem correctly but could you try adding a zero in the measure formula like follows:
dayRunSum = CALCULATE( SUM('Opportunity Product'[TotalPrice]), FILTER( ALLSELECTED('Calendar'), [Year] = MIN('Calendar'[Year]) && [posDayNoOfYr] <= MIN('Calendar'[posDayNoOfYr]))) + 0I am assuming that the value that do show up (in the current screenshot) are correct.
And make sure you have posDayNoOfYr from Calendar Table in the X-axis.
Here we are overriding the Power BI's default feature of returning blanks (when there is no data) by explicitly doing an addition with 0.
Hope it helps and if does not we can break down the requirement further!
Now, the only thing is that the summation increases as posDayNoOfYear increases, meaning it is backwards. It should be increasing as the x axis moves towards zero (dec 31st). Any idea how to flip this?
Well I am just going ahead with the blunt requirements as I do not totally understand the context of data but this should flip it:
dayRunSum = CALCULATE( SUM('Opportunity Product'[TotalPrice]), FILTER( ALLSELECTED('Calendar'), 'Calendar'[Year] = MIN('Calendar'[Year]) && 'Calendar'[posDayNoOfYr] >= MIN('Calendar'[posDayNoOfYr]) ) ) + 0
You're awesome! It worked! I have never seen anything like that before. What is that doing behind the scenes?
Hey Anon2020 ,
Happy to help!
About the behind the hood operation, let's take an example of two tables (a dimension table for Product and a Sales fact table both connected by ProductId):
ProductId ProductName 1
Pro1 2 Pro2 3 Pro3 And,
ProductId SalesAmount 1 20 1 30 3 45 When you write a simple measure such as TotalSales = SUM(Sales[SalesAmount]) and then make a table or matrix visual with ProductName from Product table and this SUM measure, it evaluates as below:
ProductName TotalSales Pro1 SUM of 20 and 30 (i.e. values associated with ProductId 1) = 50 Pro2 No values related to Id 2 so it returns a BLANK() Pro3 Sum of just one value = 45 Now, Power BI by default filters out the BLANK() value so in the end you get something like this:
ProductName TotalSales Pro1 50 Pro3 45 When you explicitly add a zero to the measure with SUM(Sales[SalesAmount]) + 0, your evaluation becomes something like follows:
ProductName TotalSales Pro1 20+30+0 = 50 Pro2 BLANK() + 0 = 0 Pro3 45 + 0 = 45 If you notice this time it is 0 and not BLANK() for the second record, so Power BI will have to show it and you get something like following:
ProductName Pro1 50 Pro2 0 Pro3 45 I hope this clarifies things.
If you are interested in studying it in more detail, SQLBI has a detailed article for this: https://www.sqlbi.com/articles/how-to-return-0-instead-of-blank-in-dax/Cheers!
