Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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.
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.
@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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |