Dealing with Bank Accounts that Change from Department to Department in a Daily Cash Report

[tuta23]

Setup:

I am trying to set up a very simple end of day cash balance report.   Fact table will be a list of all bank accounts and end of day balance.  I have lookup tables keyed on account number to link things like Bank Name, Country, and Department.

Problem:

Over the course of the year, any single account may be moved from department to department.

So, account 1111 might be in Department A for 1/1/2020 - 5/15/2020, and then moved to Department B for 5/16/2020-onward.

When I am setting up visuals, I want to ensure that when user is selecting 1/1-5/15, that account shows in Dept A, and likewise from 5/16 forward, shows up in Dept. B.

What is best practice for setting something like this up with effective dates?  Thanks so much!

Example of Lookup table:

[tuta23]

Let me know if anything is unclear.  Thanks!

[aj1973]

Hi @tuta23 

The last image you sent is helpful but still not clear so Let's do this step by step please.

The card on the right add the Cash balance in it and then go to the filter pane and add department and date then try to play with the filters until you get something close to what you are looking for.

Let me know please.

[aj1973]

Indeed, the issue is not clear to me as well as for the rest of the community. Can you share a dummy Pibx, or the visuals like what do you see and what you want to see. More details can help all the viewers to understand your issue.

Thanks

[tuta23]

Adding a mock up of the visuals I'm trying to plan for.

Thanks for any help!

[v-yingjl · Accepted solution]

Hi @tuta23 ,

You can try to create measures like these:

Department A = 
IF (
    SELECTEDVALUE ( 'Date'[Date] )
        <= CALCULATE ( MAX ( 'Table'[Date] ), 'Table'[Department] = "Department A" ),
    CALCULATE (
        SUM ( 'Table'[Cash Balance] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Department] = "Department A"
                && 'Table'[Date] <= SELECTEDVALUE ( 'Date'[Date] )
        )
    )
)

Department B = 
IF (
    SELECTEDVALUE ( 'Date'[Date] )
        > CALCULATE ( MAX ( 'Table'[Date] ), 'Table'[Department] = "Department A" )
        && SELECTEDVALUE ( 'Date'[Date] )
            <= CALCULATE ( MAX ( 'Table'[Date] ), 'Table'[Department] = "Department B" ),
    CALCULATE (
        SUM ( 'Table'[Cash Balance] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Department] = "Department B"
                && 'Table'[Date] <= SELECTEDVALUE ( 'Date'[Date] )
                && 'Table'[Date]
                    > CALCULATE (
                        MAX ( 'Table'[Date] ),
                        FILTER ( ALL ( 'Table' ), 'Table'[Department] = "Department A" )
                    )
        )
    )
)

Department C = 
IF (
    SELECTEDVALUE ( 'Date'[Date] )
        > CALCULATE ( MAX ( 'Table'[Date] ), 'Table'[Department] = "Department A" )
        && SELECTEDVALUE ( 'Date'[Date] )
            > CALCULATE ( MAX ( 'Table'[Date] ), 'Table'[Department] = "Department B" )
        && SELECTEDVALUE ( 'Date'[Date] )
            <= CALCULATE ( MAX ( 'Table'[Date] ), 'Table'[Department] = "Department C" ),
    CALCULATE (
        SUM ( 'Table'[Cash Balance] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Department] = "Department C"
                && 'Table'[Date] <= SELECTEDVALUE ( 'Date'[Date] )
                && 'Table'[Date]
                    > CALCULATE (
                        MAX ( 'Table'[Date] ),
                        FILTER ( ALL ( 'Table' ), 'Table'[Department] = "Department A" )
                    )
                && 'Table'[Date]
                    > CALCULATE (
                        MAX ( 'Table'[Date] ),
                        FILTER ( ALL ( 'Table' ), 'Table'[Department] = "Department B" )
                    )
        )
    )
)

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aj1973]

Hi @tuta23 

May I ask you if you are using a date slicer? Do you have a Calendar date in your model? how are you using your visual? can you share a sample PBIX?

[tuta23]

I am using a date slicer.   

Date Table:

Daily Cash Balance --- each account every day of the year: