Help with cumulative total, sum if, else ...

[Anonymous]

Hello -

I have some snapshots of inventory on a monthly basis. I also have some MRP data, which is forward looking. I have merged the data in sql/power bi and am now trying to do the running total/cumulative sum through power bi.

Given the nature of how the data is setup through the merge, prior to today is snapshots (where a snapshot is the inventory at that time), while today or beyond is either a plus or minus. 

I would like to setup a measure to take the qty as is from the snapshots if its date is < today, and start a running total if the date is today or beyond. Below, what I have so far, will pass a null if <today, and it needs a slight tweak to display the qty from the snapshot. Table = 'Data'[Qty]. I also have a measure just called [Qty], and that would work as well.

Cumulative Qty =

CALCULATE([Qty],

FILTER(ALLSELECTED('CALENDAR'),

'CALENDAR'[Date] >= TODAY() &&

'CALENDAR'[Date] <= MAX('CALENDAR'[Date])))

Thanks Nick

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try this:

Cumulative Qty =
VAR _value =
    CALCULATE (
        [Qty],
        FILTER (
            ALLSELECTED ( 'CALENDAR' ),
            'CALENDAR'[Date] >= TODAY ()
                && 'CALENDAR'[Date] <= MAX ( 'CALENDAR'[Date] )
        )
    )
RETURN
    IF ( _value = null, [Qty], _value )

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Eyelyn Qin

[Anonymous]

Hello @Anonymous - thank you. I marked it as solution and only had to make a minor tweak, replacing null with blank().

N

[Anonymous]

Hi @Anonymous 

Oh... yes, you are right! Sorry for my carelessness.

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Try like

CALCULATE([Qty],
FILTER(ALLSELECTED('CALENDAR'),
'CALENDAR'[Date] >= TODAY()-1 &&
'CALENDAR'[Date] <= MAX('CALENDAR'[Date])))

[Anonymous]

@amitchandak Thanks for your attention. Here is a quick screenshot. The formula is up top. Results on the bottom. The numbers next to the green, are perfect. where is it is yellow, i want those rows to read 51, 55, 37 instead of NULL, under the cumulative qty column.

N