cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

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:

sample_lookup_dept_eff_dates.JPG

1 ACCEPTED SOLUTION
Community Support
Community Support

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" )
                    )
        )
    )
)

departmen.png

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.

View solution in original post

7 REPLIES 7
Frequent Visitor

Let me know if anything is unclear.  Thanks!

Memorable Member
Memorable Member

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.

 

Regards
Amine Jerbi
https://www.linkedin.com/in/amine-jerbi-2b3a8928/

Did I answer your question? Mark my post as a solution!
Memorable Member
Memorable Member

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

Regards
Amine Jerbi
https://www.linkedin.com/in/amine-jerbi-2b3a8928/

Did I answer your question? Mark my post as a solution!
Frequent Visitor

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

 

Thanks for any help!

example_visuals.JPG

Community Support
Community Support

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" )
                    )
        )
    )
)

departmen.png

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.

View solution in original post

Memorable Member
Memorable Member

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?

 

Regards
Amine Jerbi
https://www.linkedin.com/in/amine-jerbi-2b3a8928/

Did I answer your question? Mark my post as a solution!
Frequent Visitor

I am using a date slicer.   

 

Date Table:

exam_date_table.JPG

 

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

daily_cash_bal_each_account.JPG

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors