Forum Discussion
Rolling 12 month total
I have some data which shows the date a plan was issued and whether it was issued on time or late (data below) - at the moment the data has plans issued back to Jan 2018 but this may be extended later
I have graphs which show the numbers and percentages that were issued on time each month, but I need to also be able to show the rolling 12 month total - I've serached but can't find a solution that I can get to work (I am a novice power BI user so am struggling a little to retro-fit some of the suggestions)
Ideally I would like to be able to show a variety of graphs inlcuding secondary axis (see picture for a selection of graphs pulled together in excel), but also have a date slicer so that users can pick their own period to view
I have a large calendar table available with various date calculations with index dates going from 01/01/1950 through to 31/12/2050
Any help gratefully received
Thanks 🙂
4 Replies
Here is the sample data
Completed Date ID On Time? 02/01/2018 74199 Issued Late 02/01/2018 135527 Issued Late 02/01/2018 171669 Issued Late 03/01/2018 61538 Issued Late 03/01/2018 108135 Issued Late 03/01/2018 108659 Issued on Time 03/01/2018 176304 Issued Late 03/01/2018 186854 Issued Late 08/01/2018 137803 Issued Late 09/01/2018 131307 Issued Late 09/01/2018 149297 Issued Late 09/01/2018 172292 Issued Late 09/01/2018 175415 Issued Late 10/01/2018 56709 Issued Late 10/01/2018 107016 Issued Late 10/01/2018 113320 Issued Late 12/01/2018 80528 Issued Late 12/01/2018 129652 Issued Late 15/01/2018 168704 Issued Late 17/01/2018 170877 Issued Late 18/01/2018 135823 Issued on Time 18/01/2018 189684 Issued Late 22/01/2018 169977 Issued Late 24/01/2018 117956 Issued Late 25/01/2018 85218 Issued Late 28/01/2018 173800 Issued on Time 29/01/2018 118641 Issued Late 29/01/2018 118642 Issued Late 29/01/2018 163250 Issued on Time 30/01/2018 139510 Issued Late 30/01/2018 150284 Issued Late 31/01/2018 105258 Issued Late 31/01/2018 117433 Issued Late 05/02/2018 146625 Issued on Time 05/02/2018 160985 Issued Late 06/02/2018 88403 Issued Late 06/02/2018 113419 Issued Late 06/02/2018 137660 Issued Late 06/02/2018 175074 Issued Late 06/02/2018 185855 Issued Late 06/02/2018 191901 Issued Late 07/02/2018 118395 Issued on Time 07/02/2018 159842 Issued Late 07/02/2018 169744 Issued Late 09/02/2018 124823 Issued Late 12/02/2018 118757 Issued Late 12/02/2018 160193 Issued Late 12/02/2018 162288 Issued on Time 12/02/2018 186487 Issued Late 13/02/2018 151749 Issued on Time 13/02/2018 173399 Issued Late 13/02/2018 173919 Issued Late 14/02/2018 151319 Issued on Time 15/02/2018 145754 Issued on Time 15/02/2018 150684 Issued Late 15/02/2018 176530 Issued Late 21/02/2018 118495 Issued Late 22/02/2018 157091 Issued Late 22/02/2018 192944 Issued Late 26/02/2018 150545 Issued Late 26/02/2018 161906 Issued Late 26/02/2018 180552 Issued Late 28/02/2018 108907 Issued Late 28/02/2018 191903 Issued Late 05/03/2018 77259 Issued on Time 05/03/2018 109262 Issued Late 05/03/2018 126208 Issued Late 05/03/2018 170580 Issued Late 06/03/2018 155220 Issued Late 06/03/2018 179782 Issued Late 07/03/2018 13451 Issued Late 08/03/2018 148851 Issued on Time 09/03/2018 151751 Issued Late 09/03/2018 177687 Issued on Time 09/03/2018 179979 Issued Late 12/03/2018 97334 Issued Late 12/03/2018 117174 Issued on Time 12/03/2018 130213 Issued Late 13/03/2018 84406 Issued Late 13/03/2018 131073 Issued on Time 13/03/2018 196434 Issued Late 14/03/2018 175440 Issued on Time 15/03/2018 70967 Issued Late 19/03/2018 172801 Issued Late 20/03/2018 85739 Issued Late 22/03/2018 93943 Issued on Time 22/03/2018 175961 Issued Late 26/03/2018 85646 Issued Late 26/03/2018 161540 Issued Late 26/03/2018 168472 Issued Late 27/03/2018 83412 Issued Late 27/03/2018 122662 Issued Late 29/03/2018 114942 Issued Late 29/03/2018 184929 Issued Late Hi Anonymous ,
You can create the measures as below:
Issued on Time = CALCULATE(DISTINCTCOUNT('Table'[ID]),'Table'[On Time?]="Issued on Time")Issued on Time(R12) = CALCULATE([Issued on Time], DATESBETWEEN ( 'Calendar'[Date], NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Calendar'[Date] ) ) ), LASTDATE ( 'Calendar'[Date] ) ) )Issued Late = CALCULATE(DISTINCTCOUNT('Table'[ID]),'Table'[On Time?]="Issued Late")Issued Late(R12) = CALCULATE([Issued Late],DATESBETWEEN ( 'Calendar'[Date], NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Calendar'[Date] ) ) ), LASTDATE ( 'Calendar'[Date] ) ) )Issue on Time % = DIVIDE( [Issued on Time],([Issued on Time]+[Issued Late]))Issue on Time(R12) % = DIVIDE( [Issued on Time(R12)],([Issued on Time(R12)]+[Issued Late(R12)]))I just created a sample pbix file, you can get it from this link.
Best Regards
Rena
Hi Rena
That is really helpful for the most part - couple of issues which don't quite seem to be working right which I can't quite work out how to fix...
In your example, it is picking up data from 2017 even though the data set only goes back to Jan 2018.
In my version of it, it is not doing that for most graphs but it is for the comb-graph for issued on time in month and over a rolling 12 it is pulling in all the dates in my date calendar, even when a specific date is selected - all of the other graphs are filtering fine - any ideas?
Thanks 🙂
Hi Anonymous ,
Sorry for replying late. Could you please give me an example to tell which calculated values are incorrect and what should be the correct values? Also, where does the date field applied on slicer come from? If it is convenient, could you please provide your pbix file directly in order to make troubleshooting based on your actual scenario?
Best Regards
Rena