cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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])
)
)

 

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
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 IDProject IDBaseline StartBaseline FinishForecast StartForecast FinishActual StartActual Finish
ID1Area 117 May 202019 Aug 202120 May 202017 May 202022 May 202025 May 2020
ID2Area 122 Nov 20191 July 202019 Nov 201919 July 202019 Nov 201915 Jun 2020
ID3Area 231 July 202031 Aug 202026 July 202026 July 2020  
ID4Area 312 Sep 202118 Sep 202125 Sep 202125 Sep 2021  

 

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

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors