cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Calculate a running total to a specific date with inactive relationship

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
3 REPLIES 3
Highlighted
Super User IX

## Re: Calculate a running total to a specific date with inactive relationship

@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])
)
)``````

Proud to be a Super User!

Highlighted
Community Support

## Re: Calculate a running total to a specific date with inactive relationship

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.
Highlighted
Regular Visitor

## Re: Calculate a running total to a specific date with inactive relationship

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.