Forum Discussion
Cumulative Sales in Two Date Ranges
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!
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!