cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LaurenceSD
Helper II
Helper II

Cumulative Formula for customer Projection - not quite right?

Hi,

 

I'm looking for a bit of help, I'm trying to use a line graph to show This Year, Last Year, the year before and Budget, with a TD and then further figure. All of which is working fine, until I get to the Projection for the rest of this year. The way i want the Projection to work, is to first make a comparison of This Year vs Last Year-1 (because last year was a washout) over the last 4 weeks and then take the % growth and apply that to the further period for the year before. So in the example I've got, I've just stuck a manual % in to test out getting this to work, so I've put in that for the last 4 weeks we've been running at 90% of the year before, so therefore if we project out doing the same of ver the rest of the month, where does that get us to.

 

LaurenceSD_0-1621247899065.png

The problem i've got is that the formula instead of adding on the correct column wchich shoud be Bkgs TC Proj Calc, is seems to be, somehow just taking 90% off the cumulative total. So on 18th May it jumps from 122 bookings in the final column to 350, whereas the Projection suggests it should only jump to 130.

 

Here are my relevant formulas

 

Bkgs TY = COUNTROWS(PBI_BookingsSales)
 
Bkgs TY TD =
if(max(Dates[Date])<=today(),
CALCULATE(
    [Bkgs TY],
    FILTER(
        ALLSELECTED('Dates'[Date]),
        ISONORAFTER('Dates'[Date], MAX('Dates'[Date]), DESC)
    )
),BLANK())
 
Bkgs LY-1 = CALCULATE([Bkgs LY],SAMEPERIODLASTYEAR(Dates[Date]))
 
Bkgs LY-1 TD =
if(max(Dates[Date])<=today(),
CALCULATE(
    [Bkgs LY-1],
    FILTER(
        ALLSELECTED('Dates'[Date]),
        ISONORAFTER('Dates'[Date], MAX('Dates'[Date]), DESC)
    )
),BLANK())
 
Bkgs LY-1 Frthr =
if(max(Dates[Date])>=today(),
CALCULATE(
    [Bkgs LY-1],
    FILTER(
        ALLSELECTED('Dates'[Date]),
        ISONORAFTER('Dates'[Date], MAX('Dates'[Date]), DESC)
    )
),BLANK())
 
Bkgs TY Proj Calc =
if(MAX(Dates[Date])<=today(),[Bkgs TY],
([Bkgs LY-1]*(0.9)))
 
Bkgs TY Proj =

CALCULATE(
    [Bkgs TY Proj Calc],
    FILTER(
        ALLSELECTED('Dates'[Date]),
        ISONORAFTER('Dates'[Date], MAX('Dates'[Date]), DESC)
    )
)
 
So this final formula would normally have an if statement in it to stop it showing for dates before today, but i took it out to show better on the table what's going on.
 
Any guidance in where i'm going wrong would be greatly appreciated.
 
If you need the PBI file, i can anonymise the data and send it over
 
Thanks
 
Laurence
 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

Hi @LaurenceSD ,

You can refer the content in the following links to achieve it:

Cumulative Total/ Running Total in Power BI

DAX Cumulative for a filtered year and previous year

Cumulative total for current week, current month and current Year

Besides that, you can share some sample data in table PBI_BookingsSales (exclude sensitive data) with Text format in order to provide you a suitable solution. Thank you.

Best Regards

Community Support Team _ Rena
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

2 REPLIES 2
LaurenceSD
Helper II
Helper II

thanks @yingyinr I managed to find the solution, after a bit of time away from the report, was fairly simple in the end, my base calculation was wrong

 

Bkgs TY Proj Calc =
if(MAX(Dates[Date])<=today(),[Bkgs TY],
([Bkgs LY-1]*(0.9)))
 
Should have been simply Bkgs TY+([Bkgs LY-1]*0.9) - I'm still struggling with the formula to replace the 0.9 with a dynamic calculation that takes the last 4 weeks from this year and last year and compares the growth, where this calculation needs if it was plotted on a graph with a date axis to be a straight line and not vary with the dates, but I have another thread open on that one.
yingyinr
Community Support
Community Support

Hi @LaurenceSD ,

You can refer the content in the following links to achieve it:

Cumulative Total/ Running Total in Power BI

DAX Cumulative for a filtered year and previous year

Cumulative total for current week, current month and current Year

Besides that, you can share some sample data in table PBI_BookingsSales (exclude sensitive data) with Text format in order to provide you a suitable solution. Thank you.

Best Regards

Community Support Team _ Rena
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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.