Cumulative Sales in Two Date Ranges

[Anon2020]

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 

dayRunSum =

CALCULATE(

    SUM('Opportunity Product'[TotalPrice]),

    FILTER(

        ALLSELECTED('Calendar'), [Year] = MIN('Calendar'[Year]) && [posDayNoOfYr] <= MIN('Calendar'[posDayNoOfYr])))

which does not work either. Any help would be greatly appreciated!

[Anon2020]

You're awesome! It worked! I have never seen anything like that before. What is that doing behind the scenes?

[alish_b]

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!

[alish_b · Accepted solution]

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]))) + 0

I 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!

[Anon2020]

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?

[alish_b]

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