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 have a formula which calculates the cumulative QTD target figures like so
Cumulative Q BPD Target = TOTALQTD('Targets'[target],'Full Date Table'[Date])
where full date table is a calendar table.
In my table showing week commencing as the rows and cumulative target as the values it is correct for every week except WC 26th March where it is blank.
I am assuming this is down to the fact WC 26th March finishes on 1st April and therefore there is a one day overlap between the end of Q1 and the beginning of what Power BI thinks is Q2 but I don't know how to fix this.
Any ideas?
Can you supply sample data to recreate? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Also, you might be able to use my Quick Measures here: https://community.powerbi.com/t5/Quick-Measures-Gallery/First-Last-Working-Days/m-p/391545
Sure.
I have re-created the problem with sample data on a simpler dataset. Can't use my actua; data.
I also have a calendar table which joins to this using WC = date.
Then use the formula for totalqtd to create a cumulative target for each quarter. As I say though it works for every week for Q1 except WC 26th March where it doesn't appear in the table.
I still think t's down to power bi not understanding this week is still Q1 despite the Sunday being 1st March
Can you paste that as text? Right click, Copy Table.
YearQuarterMonthWeekWCTarget
2018 | 1 | 1 | 1 | 01/01/2018 | 100 |
2018 | 1 | 1 | 2 | 08/01/2018 | 200 |
2018 | 1 | 1 | 3 | 15/01/2018 | 200 |
2018 | 1 | 1 | 4 | 22/01/2018 | 200 |
2018 | 1 | 1 | 5 | 29/01/2018 | 300 |
2018 | 1 | 2 | 6 | 05/02/2018 | 300 |
2018 | 1 | 2 | 7 | 12/02/2018 | 300 |
2018 | 1 | 2 | 8 | 19/02/2018 | 300 |
2018 | 1 | 2 | 9 | 26/02/2018 | 300 |
2018 | 1 | 3 | 10 | 05/03/2018 | 100 |
2018 | 1 | 3 | 11 | 12/03/2018 | 200 |
2018 | 1 | 3 | 12 | 19/03/2018 | 200 |
2018 | 1 | 3 | 13 | 26/03/2018 | 200 |
2018 | 2 | 4 | 1 | 02/04/2018 | 300 |
2018 | 2 | 4 | 2 | 09/04/2018 | 300 |
2018 | 2 | 4 | 3 | 16/04/2018 | 300 |
2018 | 2 | 4 | 4 | 23/04/2018 | 300 |
2018 | 2 | 4 | 5 | 30/04/2018 | 300 |
2018 | 2 | 5 | 6 | 07/05/2018 | 100 |
2018 | 2 | 5 | 7 | 14/05/2018 | 200 |
2018 | 2 | 5 | 8 | 21/05/2018 | 200 |
2018 | 2 | 5 | 9 | 28/05/2018 | 200 |
2018 | 2 | 6 | 10 | 04/06/2018 | 300 |
2018 | 2 | 6 | 11 | 11/06/2018 | 300 |
2018 | 2 | 6 | 12 | 18/06/2018 | 300 |
2018 | 2 | 6 | 13 | 25/06/2018 | 300 |
2018 | 3 | 7 | 1 | 02/07/2018 | 300 |
2018 | 3 | 7 | 2 | 09/07/2018 | 100 |
2018 | 3 | 7 | 3 | 16/07/2018 | 200 |
2018 | 3 | 7 | 4 | 23/07/2018 | 200 |
2018 | 3 | 7 | 5 | 30/07/2018 | 200 |
2018 | 3 | 8 | 6 | 06/08/2018 | 300 |
2018 | 3 | 8 | 7 | 13/08/2018 | 300 |
2018 | 3 | 8 | 8 | 20/08/2018 | 300 |
2018 | 3 | 8 | 9 | 27/08/2018 | 300 |
2018 | 3 | 9 | 10 | 03/09/2018 | 300 |
2018 | 3 | 9 | 11 | 10/09/2018 | 100 |
2018 | 3 | 9 | 12 | 17/09/2018 | 200 |
2018 | 3 | 9 | 13 | 24/09/2018 | 200 |
2018 | 4 | 10 | 1 | 01/10/2018 | 200 |
2018 | 4 | 10 | 2 | 08/10/2018 | 300 |
2018 | 4 | 10 | 3 | 15/10/2018 | 300 |
2018 | 4 | 10 | 4 | 22/10/2018 | 300 |
2018 | 4 | 10 | 5 | 29/10/2018 | 300 |
2018 | 4 | 11 | 6 | 05/11/2018 | 300 |
2018 | 4 | 11 | 7 | 12/11/2018 | 100 |
2018 | 4 | 11 | 8 | 19/11/2018 | 200 |
2018 | 4 | 11 | 9 | 26/11/2018 | 200 |
2018 | 4 | 12 | 10 | 03/12/2018 | 200 |
2018 | 4 | 12 | 11 | 10/12/2018 | 300 |
2018 | 4 | 12 | 12 | 17/12/2018 | 300 |
2018 | 4 | 12 | 13 | 24/12/2018 | 300 |
2018 | 1 | 1 | 1 | 01/01/2018 | 300 |
2018 | 1 | 1 | 2 | 08/01/2018 | 300 |
2018 | 1 | 1 | 3 | 15/01/2018 | 100 |
2018 | 1 | 1 | 4 | 22/01/2018 | 200 |
2018 | 1 | 1 | 5 | 29/01/2018 | 200 |
2018 | 1 | 2 | 6 | 05/02/2018 | 200 |
2018 | 1 | 2 | 7 | 12/02/2018 | 300 |
2018 | 1 | 2 | 8 | 19/02/2018 | 300 |
2018 | 1 | 2 | 9 | 26/02/2018 | 300 |
2018 | 1 | 3 | 10 | 05/03/2018 | 300 |
2018 | 1 | 3 | 11 | 12/03/2018 | 300 |
2018 | 1 | 3 | 12 | 19/03/2018 | 100 |
2018 | 1 | 3 | 13 | 26/03/2018 | 200 |
2018 | 2 | 4 | 1 | 02/04/2018 | 200 |
2018 | 2 | 4 | 2 | 09/04/2018 | 200 |
2018 | 2 | 4 | 3 | 16/04/2018 | 300 |
2018 | 2 | 4 | 4 | 23/04/2018 | 300 |
2018 | 2 | 4 | 5 | 30/04/2018 | 300 |
2018 | 2 | 5 | 6 | 07/05/2018 | 300 |
2018 | 2 | 5 | 7 | 14/05/2018 | 300 |
2018 | 2 | 5 | 8 | 21/05/2018 | 100 |
2018 | 2 | 5 | 9 | 28/05/2018 | 200 |
2018 | 2 | 6 | 10 | 04/06/2018 | 200 |
2018 | 2 | 6 | 11 | 11/06/2018 | 200 |
2018 | 2 | 6 | 12 | 18/06/2018 | 300 |
2018 | 2 | 6 | 13 | 25/06/2018 | 300 |
2018 | 3 | 7 | 1 | 02/07/2018 | 300 |
2018 | 3 | 7 | 2 | 09/07/2018 | 300 |
2018 | 3 | 7 | 3 | 16/07/2018 | 300 |
2018 | 3 | 7 | 4 | 23/07/2018 | 100 |
2018 | 3 | 7 | 5 | 30/07/2018 | 200 |
2018 | 3 | 8 | 6 | 06/08/2018 | 200 |
2018 | 3 | 8 | 7 | 13/08/2018 | 200 |
2018 | 3 | 8 | 8 | 20/08/2018 | 300 |
2018 | 3 | 8 | 9 | 27/08/2018 | 300 |
2018 | 3 | 9 | 10 | 03/09/2018 | 300 |
2018 | 3 | 9 | 11 | 10/09/2018 | 300 |
2018 | 3 | 9 | 12 | 17/09/2018 | 300 |
2018 | 3 | 9 | 13 | 24/09/2018 | 100 |
2018 | 4 | 10 | 1 | 01/10/2018 | 200 |
2018 | 4 | 10 | 2 | 08/10/2018 | 200 |
2018 | 4 | 10 | 3 | 15/10/2018 | 200 |
2018 | 4 | 10 | 4 | 22/10/2018 | 300 |
2018 | 4 | 10 | 5 | 29/10/2018 | 300 |
2018 | 4 | 11 | 6 | 05/11/2018 | 300 |
2018 | 4 | 11 | 7 | 12/11/2018 | 300 |
2018 | 4 | 11 | 8 | 19/11/2018 | 300 |
2018 | 4 | 11 | 9 | 26/11/2018 | 100 |
2018 | 4 | 12 | 10 | 03/12/2018 | 200 |
2018 | 4 | 12 | 11 | 10/12/2018 | 200 |
2018 | 4 | 12 | 12 | 17/12/2018 | 200 |
2018 | 4 | 12 | 13 | 24/12/2018 | 300 |
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 |
---|---|
115 | |
99 | |
88 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |