Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Backward calculate backlog

I have the table below in Power BI ("table1") and I need to calculate the values in red working backwards. calculation for the "known" backlog value for tiday. xOpenCountToday = CALCULATE(count(_A...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    You can create a measure as belwo to get it, please find the details in the attachment.

    Backlog = 
VAR _seldate =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _selname =
    SELECTEDVALUE ( 'Table'[Name] )
VAR _culincome =
    CALCULATE (
        SUM ( 'Table'[Incoming] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Name] = _selname
                && 'Table'[Date] > _seldate
        )
    )
VAR _culoutcome =
    CALCULATE (
        SUM ( 'Table'[Outgoing] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Name] = _selname
                && 'Table'[Date] > _seldate
        )
    )
RETURN
    [xOpenCountToday] + _culoutcome - _culincome

    Best Regards

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Anonymous ,

You can create a measure as belwo to get it, please find the details in the attachment.

Backlog = 
VAR _seldate =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _selname =
    SELECTEDVALUE ( 'Table'[Name] )
VAR _culincome =
    CALCULATE (
        SUM ( 'Table'[Incoming] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Name] = _selname
                && 'Table'[Date] > _seldate
        )
    )
VAR _culoutcome =
    CALCULATE (
        SUM ( 'Table'[Outgoing] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Name] = _selname
                && 'Table'[Date] > _seldate
        )
    )
RETURN
    [xOpenCountToday] + _culoutcome - _culincome

Best Regards

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thank you Rena. That appears to be working - Thank you so much 👍