Forum Discussion

Anon2020's avatar
Anon2020
Helper I
1 year ago
Solved

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, f...
Need Help
Tutorial Requests
  • alish_b's avatar
    alish_b
    1 year ago

    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's avatar
Anon2020
Helper I
1 year ago

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's avatar
alish_b
Super User
1 year ago

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