Measure to show target value at a point in time based on other DAX measures

[christinaxxx]

I want to get the cumulative target at each point in time (red line) as an average trend. This is used to track how many customers each sales manager has onboarded to our new tech app. It sounds quite simple but I couldn't wrap my head around it. 

The blue line is the total number of customer onboarded per week. Sample data table look like the below. It's summaried by manager by customer type per week. The start date is 2/11/2023 and the start value is not zero because I started tracking this half way through. The DAX I wrote for the blue line is WeeklyTotal = SUM(Fact_WeeklySummary[Customer Count])

The target number is 90% of the customer base. The end date is 31/7/2024. The count of customer in the customer base is changing because every week there are new and exited customers. The DAX I wrote is Target = 0.9 * COUNTROWS(Dim_Customer)

And on top of the above, the red line will need to change as I change the slicer (Customer Type & Manager ID) and will include all date(or week) up to 31/7/2024 on the x-axis.

[Anonymous]

Hi @christinaxxx ,

Is this your expected output?

If so, pelase try this:

Measure = 
VAR _numerator = CALCULATE([WeeklyTotal],'Fact_WeeklySummary'[WeekOf]<=MAX('Fact_WeeklySummary'[WeekOf]))
VAR _denominator = COUNTROWS(FILTER(ALLSELECTED('Fact_WeeklySummary'[WeekOf]),'Fact_WeeklySummary'[WeekOf]<=MAX('Fact_WeeklySummary'[WeekOf])))
VAR _result = DIVIDE(_numerator,_denominator)
RETURN
_result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[christinaxxx]

Thank you. But I want the weekly incremental value to be the same. Something like (end value - start value) / count of weeks and then you add this weekly incremental value to last's week target value. I know the start and end values and they are measures.

[amitchandak · Accepted solution]

@christinaxxx , Try a measure like

CumulativeTargetTrend =
VAR _CurrentDate = MAX('Date'[Date])
VAR _StartDate = DATE(2023,2,11) // You can use Minx(allselected('Date'), 'Date'[Date])
VAR _EndDate = DATE(2024,7,31) // You can use Maxx(allselected('Date'), 'Date'[Date])
VAR WeeksElapsed = DATEDIFF(_StartDate, _CurrentDate, WEEK)
VAR TotalTarget = CALCULATE([Target], ALLSELECTED('Date'), 'Date'[Date] <= _CurrentDate)
RETURN IF(WeeksElapsed > 0, TotalTarget / WeeksElapsed, BLANK())

Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

[christinaxxx]

Thanks @amitchandak  I used your solution with some minor twists and now it's working 🙂

[christinaxxx]

Thank you. Not sure where I went wrong but my graph is like this