Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am trying to get monthly cummulative total % from following data model.
ID | StartDate | CompleteDate | StartedFlag | CompletedFlag |
15875 | 10/10/2019 | 1 | 0 | |
103850 | 10/10/2019 | 1 | 0 | |
19959 | 10/10/2019 | 1 | 0 | |
62319 | 10/23/2019 | 8/6/2019 | 1 | 1 |
63692 | 10/10/2019 | 10/18/2019 | 1 | 1 |
102010 | 10/3/2019 | 1 | 0 | |
32005 | 10/4/2019 | 1 | 0 | |
123864 | 10/4/2019 | 1 | 0 | |
41897 | 10/4/2019 | 1 | 0 | |
123861 | 10/4/2019 | 1 | 0 | |
17147 | 10/4/2019 | 10/29/2019 | 1 | 1 |
31731 | 10/2/2019 | 10/11/2019 | 1 | 1 |
53614 | 10/4/2019 | 10/17/2019 | 1 | 1 |
123076 | 10/16/2019 | 1 | 0 | |
45862 | 10/3/2019 | 10/11/2019 | 1 | 1 |
1919 | 10/4/2019 | 10/17/2019 | 1 | 1 |
Then need to get count of started and cummelative total of completed over the weeks from start date.
example of what I am trying to build would be similar to below.
Month | # of Started | Weeks from StartDate | Cummlative Total # of Complete | Cummlative % (Cumm#Complete/#ofStarted) |
jan | 200 | 1 | 0 | 0% |
jan | 200 | 2 | 5 | 3% |
jan | 200 | 3 | 10 | 5% |
jan | 200 | 4 | 80 | 40% |
jan | 200 | 5 | 90 | 45% |
jan | 200 | 6 | 100 | 50% |
jan | 200 | 7 | 103 | 52% |
jan | 200 | 8 | 105 | 53% |
jan | 200 | 10 | 106 | 53% |
jan | 200 | 11 | 108 | 54% |
feb | 350 | 1 | 1 | 0% |
feb | 350 | 2 | 9 | 3% |
feb | 350 | 3 | 20 | 6% |
feb | 350 | 4 | 25 | 7% |
feb | 350 | 5 | 35 | 10% |
feb | 350 | 6 | 89 | 25% |
feb | 350 | 7 | 97 | 28% |
feb | 350 | 8 | 111 | 32% |
feb | 350 | 10 | 231 | 66% |
feb | 350 | 11 | 239 | 68% |
feb | 350 | 12 | 240 | 69% |
Can some one help with approaching this issue, I will later need to convert this ti line chart with weeks from start as x-axis.
Sample PBIX can be downloaded from here:
Solved! Go to Solution.
Hi @Anonymous ,
I'm not clear how to calculate "count of started". Can you please explain of the logic? For getting cumulative total monthly, I think you could reference my answer from the similar thread to have a try.
https://community.powerbi.com/t5/Desktop/Cumulative-sum-that-resets-every-year/td-p/994086
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
I'm not clear how to calculate "count of started". Can you please explain of the logic? For getting cumulative total monthly, I think you could reference my answer from the similar thread to have a try.
https://community.powerbi.com/t5/Desktop/Cumulative-sum-that-resets-every-year/td-p/994086
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-xuding-msft Thanks Xue for your responce. The count of start is count of ID where started flag is 1 or Sum of started flag. Similarly cummulative monthly total is cummulative count of ID where completedflag is one or sum of CompletedFlag that was started on same month.
TIA
@Anonymous , refer if this can help you
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |