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!