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 all
i have a report on when developers complete training modules. each developer is assigned 4 modules to complete and an assigned date/time is applied to the relevant dev row
the table looks like the below
the Module columns are date/time
Developer | Module 1 | Module 2 | Module 3 | Module 4 |
Dev 1 | 05/08/2019 | |||
Dev 2 | 12/08/2019 | 05/08/2019 | ||
Dev 3 | 09/08/2019 |
what i am trying to achieve is a trend chart that shows the accumalated count of developers per module
my problem is i am unable to get the accumulated total over time but rather gives me the total completed on a specific date. also if the date/time stamp happens to be the exactly the same it isnt a distinct count
im not sure what i am doing wrong that is unable to determine the accmulated/trend count
any help would be appreciated
Solved! Go to Solution.
Hello @Anonymous
This measure uses a date table and to get the running total target.
Module 1 RT = VAR EndingDate = LASTDATE(Dates[Date]) RETURN IF ( EndingDate <= EOMONTH ( TODAY() ), CALCULATE(COUNTROWS(YourTable),YourTable[Module 1] <= EndingDate, NOT ISBLANK(YourTable[Module 1])) )
There are four of these measures, one for each module. I uploaded my sample file here ModuleRT.pbix
Hello @Anonymous
This measure uses a date table and to get the running total target.
Module 1 RT = VAR EndingDate = LASTDATE(Dates[Date]) RETURN IF ( EndingDate <= EOMONTH ( TODAY() ), CALCULATE(COUNTROWS(YourTable),YourTable[Module 1] <= EndingDate, NOT ISBLANK(YourTable[Module 1])) )
There are four of these measures, one for each module. I uploaded my sample file here ModuleRT.pbix
@jdbuchanan71
thankyou for your effort.....that works great
is there a way to show the current count of this month? as August figures wont appear
Hi @Anonymous
Sorry, I should have done the active month check against the end of the month list this.
Module 1 RT = VAR EndingDate = LASTDATE(Dates[Date]) RETURN IF ( EndingDate <= EOMONTH ( TODAY(), 0 ), CALCULATE(COUNTROWS(YourTable),YourTable[Module 1] <= EndingDate, NOT ISBLANK(YourTable[Module 1])) )
The 4th line changed to go through the end of the current month. You would just change that in each of the measures.
@jdbuchanan71
no apology needed
that is perfect.
thank you for your time. it is very much appreciated
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |