daily columns and 1 summation column

[PBINewbie920]

Hi!

I have a report which has daily orders and billings. I want to add a column for backlog (any orders that are still open) However, when I try to add it, it just gives me the daily amount, but i would like backlog to be a summation total and not a daily value like the other columns.

Example below: I want a column like in blue, where it gives me the total backlog for each day, and not the daily amount. 

Any tips?

Thanks!!

[v-chenwuz-msft · Accepted solution]

Hi @PBINewbie920 ,

Calculate column expression:

Backlog = 
var _2021totalbacklog = 500
return
SUMX(FILTER('Backlog',[Backlog]<=EARLIER('Table'[Date])),[Daily Added])+_2021totalbacklog

Measure in visual :

backlog_m =
VAR _2021totalbacklog = 500
RETURN
    SUMX (
        FILTER ( ALLSELECTED ( 'Backlog' ), [Backlog] <= MAX ( 'Table'[Date] ) ),
        [Daily Added]
    ) + _2021totalbacklog

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[amitchandak]

@PBINewbie920 , These tables need to share the common date and other dimension tables.

Then you can show this visual using common date table

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