Calculate a running total to a specific date with inactive relationship

[envysn]

Hi,

I am trying to write a measure to calculate the cumulative number of activities that have been completed on a month by month basis, but am not getting the expected result - my best guess being because the relationship between my [Actual Finish Date] column and my date table is inactive.

My data table has several date fields (baseline start and finish, forecast start and finish, and actual start and finish), as well as an activity ID. The current active relationship is between the [Forecast Finish Date] and the date table (required for calculating the forecast activity count, and cumulative forecast count).

The current DAX code I am using is below:

Cumulative Activities Actually Completed =

VAR LastSalesDate = CALCULATE(LASTDATE(ScheduleDetail[Actual Finish]), ALL(ScheduleDetail) )

RETURN

IF(SELECTEDVALUE(DateTable[Date])>LastSalesDate,BLANK(),

CALCULATE(Count(ScheduleDetail[Activity ID]),

FILTER(ALLSELECTED(DateTable),

DateTable[Date] <= MAX(ScheduleDetail[Actual Finish])

)

)

)

If I were to use the USERELATIONSHIP function how would I implement this? Or, is there a better solution?

Many thanks

[v-jayw-msft]

Hi @envysn ,

It's better to share some sample data to us so that we can help you deal with the formula.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[envysn]

Hi, 

Here is a small sample of the data I am using:

Activity ID	Project ID	Baseline Start	Baseline Finish	Forecast Start	Forecast Finish	Actual Start	Actual Finish
ID1	Area 1	17 May 2020	19 Aug 2021	20 May 2020	17 May 2020	22 May 2020	25 May 2020
ID2	Area 1	22 Nov 2019	1 July 2020	19 Nov 2019	19 July 2020	19 Nov 2019	15 Jun 2020
ID3	Area 2	31 July 2020	31 Aug 2020	26 July 2020	26 July 2020
ID4	Area 3	12 Sep 2021	18 Sep 2021	25 Sep 2021	25 Sep 2021

Note that the active relationship is between the Forecast Finish column and the date column from my Date Table. 

[amitchandak]

@envysn , it should be something like this

Cumulative Activities Actually Completed =
VAR LastSalesDate = CALCULATE(LASTDATE(ScheduleDetail[Actual Finish]), ALL(ScheduleDetail) )
RETURN
IF(SELECTEDVALUE(DateTable[Date])>LastSalesDate,BLANK(),
CALCULATE(Count(ScheduleDetail[Activity ID]),
FILTER(ALLSELECTED(DateTable),
DateTable[Date] <= MAX(ScheduleDetail[Actual Finish])
) , userelation(DateTable[Date],ScheduleDetail[Actual Finish])
)
)

refer this example : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...