Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LaurenceSD
Advocate II
Advocate 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
v-yiruan-msft
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
Advocate II
Advocate II

thanks @v-yiruan-msft 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.
v-yiruan-msft
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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.