cancel
Showing results for
Did you mean:
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.

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
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.
2 REPLIES 2
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.
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.

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors