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

Cumulative formula issue

I have a budget , an actuals and a date table which i am using to show a monthly Cumulative line chart for monthly budget vs actual data.

 

i have added the the follwing measure for a budget Cumulative value which works great as i have a value for each month :-

 

Cumulative Budget =
CALCULATE(SUM('ExcelBudget'[Budget]), Filter(ALL('Date'), 'Date'[Month] <= MAX('ExcelBudget'[Month])))

 

I have the same formula for the actuals cummulative value :- 

 

Cumulative Net =
CALCULATE(SUM('tbFactProspectWon'[NetPrice]), Filter(ALL('Date'), 'Date'[Month] <= MAX('tbFactProspectWon'[DatePaidMonth])))

 

the problem i am having is that if there are no actual values for a given month, no results are showing :-

 

Month         Budget                Budget Cumulative              Actual             Actual Cumulative 

1                  1000                    1000                                     2000                2000

2                  1000                    2000                                     1500                3500

3                  1000                    3000                                                             

4                  1000                    4000                                     1500                5000

5                  1000                    5000                                     1000                6000

6                  1000                    6000                                     1000                7000

7                  1000                    7000                                     1000                8000

 

 

 how can i force the records to show as follows :-

 

Month         Budget                Budget Cumulative              Actual             Actual Cumulative 

1                  1000                    1000                                     2000                2000

2                  1000                    2000                                     1500                3500

3                  1000                    3000                                                             3500

4                  1000                    4000                                     1500                5000

5                  1000                    5000                                     1000                6000

6                  1000                    6000                                     1000                7000

7                  1000                    7000                                     1000                8000

1 ACCEPTED SOLUTION

Accepted Solutions
v-haibl-msft Super Contributor
Super Contributor

Re: Cumulative formula issue

@damomac85

 

Please try with following measure formula.

 

Cumulative Net = 
CALCULATE (
    SUM ( 'tbFactProspectWon'[NetPrice] ),
    FILTER ( ALL ( 'Date' ), 'Date'[Month] <= MAX ( 'Date'[Month] ) )
)

Cumulative formula issue_1.jpg

 

Best Regards,

Herbert

2 REPLIES 2
v-haibl-msft Super Contributor
Super Contributor

Re: Cumulative formula issue

@damomac85

 

Please try with following measure formula.

 

Cumulative Net = 
CALCULATE (
    SUM ( 'tbFactProspectWon'[NetPrice] ),
    FILTER ( ALL ( 'Date' ), 'Date'[Month] <= MAX ( 'Date'[Month] ) )
)

Cumulative formula issue_1.jpg

 

Best Regards,

Herbert

damomac85 Frequent Visitor
Frequent Visitor

Re: Cumulative formula issue

great thanks @v-haibl-msft

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

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: 129 members 1,737 guests
Please welcome our newest community members: