cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gvg Member
Member

How to create measure for waterfall chart that begins with previous month balance

Hi folks,

 

I have a table of unregular day cash in and outflows for a few years. How do I create a measure for waterfall chart that begins with previous month balance, then showing increases/decreases for every day of the filtered month, ending with the balance at the end of the filtered month? My table looks like this:

 

Date                   CashFlow

-------                -----------

2016-Jan-1              100

2016-Jan-2              -40

2016-Mar-3             -20

2017-Feb-20           200

2017-Feb-25           120

2017-Feb-26           -30

 

If current filtered month is 2017-Feb, the waterfall chart I would expect to chart:

40    200  120  -30    330

 

where 40 is the sum of all cash flows prior to 2017-Feb-1.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: How to create measure for waterfall chart that begins with previous month balance

@gvg,

 

I've modified the .pbix. Please check again.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Community Support Team
Community Support Team

Re: How to create measure for waterfall chart that begins with previous month balance

@gvg,

 

You may refer to the following DAX.

Measure =
IF (
    MAX ( Table1[Date] )
        = CALCULATE ( MIN ( Table1[Date] ), ALLSELECTED ( Table1 ) ),
    CALCULATE (
        SUM ( Table1[CashFlow] ),
        FILTER ( ALL ( Table1[Date] ), Table1[Date] <= MAX ( Table1[Date] ) )
    ),
    SUM ( Table1[CashFlow] )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
gvg Member
Member

Re: How to create measure for waterfall chart that begins with previous month balance

@v-chuncz-msft

 

Well, this returns just one value 330 instead of all five. Nothing to chart. But thanks for the tip.

Community Support Team
Community Support Team

Re: How to create measure for waterfall chart that begins with previous month balance

@gvg,

 

Please check the .pbix attached.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
gvg Member
Member

Re: How to create measure for waterfall chart that begins with previous month balance

Thanks  @v-chuncz-msft.

 

This is very close to what I am looking for. The only thing is that I want to be able to select month and have cash flows charted step by step during that month with the beginning of month and end of month values. However, when I select month ( I made my own Calendar that includes months), it doesn't chart the initial beginning balance.

 

 

Waterfall.png

Community Support Team
Community Support Team

Re: How to create measure for waterfall chart that begins with previous month balance

@gvg,

 

I've modified the .pbix. Please check again.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
gvg Member
Member

Re: How to create measure for waterfall chart that begins with previous month balance

Yep, that is exactly what I was looking for. Thanks @v-chuncz-msft !

gvg Member
Member

Re: How to create measure for waterfall chart that begins with previous month balance

Hi again,

 

It looks like this measure works only if there is no relationship between Date fields. How to modify the measure so that it works when Table1 and Calendar fields are related through the Date field?

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 247 members 2,640 guests
Please welcome our newest community members: