Forum Discussion

johnny12321's avatar
johnny12321
New Member
2 years ago
Solved

Rolling sum based on ID and date range

Hi All,

 

I am currently attempting to run a spreadsheet where I  roll the amount outstanding per ID between a start and end date.

I have something like the one rolling amount sum

below, but require the one on the bottom.

 

Would appreciate the help, I have tried in SAS but it's giving funny results and doesn't seem to drop numbers. My dataset is 2mln rows. Hence I have reverted to Power Bi, where my knowledge is a little worse. 

 

Thanks, John 

 

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi johnny12321 ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    ROLLOING = 
VAR _selid =
    SELECTEDVALUE ( 'Table'[ID] )
VAR _sdate =
    SELECTEDVALUE ( 'Table'[Start_Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Customers] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[ID] = _selid
                && 'Table'[Start_Date] <= _sdate
        )
    )

    As you check the above screenshot, it can return the correct rolling values when ID is 2. But for ID 1, the result is not same with the one which you provided. Could you please provide the related calculation logic? Thank you.

     Best Regards

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi johnny12321 ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    ROLLOING = 
VAR _selid =
    SELECTEDVALUE ( 'Table'[ID] )
VAR _sdate =
    SELECTEDVALUE ( 'Table'[Start_Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Customers] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[ID] = _selid
                && 'Table'[Start_Date] <= _sdate
        )
    )

    As you check the above screenshot, it can return the correct rolling values when ID is 2. But for ID 1, the result is not same with the one which you provided. Could you please provide the related calculation logic? Thank you.

     Best Regards