Measure based on value of another measure at start of financial year

[SteveCarter1]

I'm struggling with a specific need to show the average trend needed between start of financial year to end of financial year to reach client target. The problem I have is it does not start at zero. The target for clients is based on the pre-existing total number of clients we already have.

I have Financial Year targets for number of clients and a transaction table recording clients.

I successfully created the following measures:

- Show the sum of the financial year target (blue line top)

- Show cumulative total over all of time of clients (orange line)

- Show how many clients they need to add at that point in time to reach target (yellow line)

- Problem measure - shows how many new total clients they need to be at to acheive target (pink/purple line)

But I'm pulling my hair out trying to figure out how to show the red line - average number of new clients needed to hit target - and this is the hard part for me - based on the number of clients at the start of the selected financial year, which is a measure in itself.

The closest I get is the pink/purple line - that measure bases its calculation on the number of clients, not at the start of the FY but at the point in time being calculated, if that makes sense?

I have a feeling I'm close but just can't see it.

Any advice would be great!

I have a sample pbix file and excel file. Not sure how to attach those? So I'll try to show it here:

Excel tabs:

Targets: 

Client data:

In My manually created Master Calendar table, I have created a DAX calculated column showing the fraction of that financial year:

(it's cludgy I know. Please don't judge. I was playing around with various calcs and just built it up)

FYTDFRAC = 
VAR __startThisYear = YEAR(DATEADD('Master Calendar'[Date],-6,MONTH))
VAR __startFYDate = IF(ISBLANK(__startThisYear),BLANK(),DATE(__startThisYear,06,30))
VAR __endThisYear = IF(ISBLANK(YEAR(DATEADD('Master Calendar'[Date],6,MONTH))),YEAR('Master Calendar'[Date]),YEAR(DATEADD('Master Calendar'[Date],6,MONTH)))
VAR __EndFYDate = DATE(__endThisYear, 06, 30)
VAR __daysInYear = DATEDIFF(__startFYDate, __EndFYDate,DAY)
VAR __daysInThisYear = DATEDIFF(__startFYDate,'Master Calendar'[Date],DAY)
RETURN
__daysInThisYear / __daysInYear

So what I get in my master calendar is this (using table visual to make it easier to display):

My Clients over all time measure is:

Clients All Time = 
CALCULATE(
    DISTINCTCOUNT('Client Data'[Client Id]),
    ALL('Master Calendar'),
    DATESBETWEEN('Master Calendar'[Date],FIRSTDATE(ALL('Master Calendar'[Date])),MAX('Master Calendar'[Date]))
)

My current, wrong measure to try to show number of new clients ageraged over the year to reach target is:

FYTD Total Clients Needed = 
                    (SUM(Targets[Target]) - [Clients All Time])
                    *
                    MAX('Master Calendar'[FYTDFRAC])
                    +
                    [Clients All Time]

[amitchandak · Accepted solution]

@SteveCarter1 , based on what I got so far, You can use closingbalanceyear

Create a date based on the FY

Date = Date(right([FY],4) ,6,30) // Target stored at the last day of year

closingbalanceyear(sum(Table[Target]), Date[Date], "6/30")  // Date table and last date of year. same target every day

Also refer

Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...

 closingbalancemonth, closingbalancequarter -https://www.youtube.com/watch?v=yPQ9UV37LOU

[SteveCarter1]

Thanks! You put me in the right direction. Didn't know about opening and closing balance functions.

My solution which works is:

FYTD Total Clients Needed = 
 VAR __startCount = OPENINGBALANCEYEAR([Clients All Time],'Master Calendar'[Date],"30/06")
 RETURN
   (SUM(Targets[Target]) - __startCount)
   *
   MAX('Master Calendar'[FYTDFRAC])
   +
   __startCount