Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a table which contains conumser complaints. the table has complaint received date and this is linked to the date when the product was made - [production date].
I need to calculate the number of complaints received by month and of those recieved in a given month, I need to calculate how many of the products were produced in the last 3 months. For example, I need complaints recieved in January, and of those complaints a count of the products which were produced in November, December and January. E.g complaints received in January might be 50, and of those 50, 30 might have been produced in Nov, Dec, January.
In addition to the data table, i have created a calendar table.
I'm going around in circles! testing dateadd, datesbewteen, datesinperiod - but I'm getting nowhere.
I need this analysis for last 4 years.
Many thanks in advance if you cna help!
Solved! Go to Solution.
I don't have your sample so I can't really test but, an idea would to harvest the min and max dates:
BeginDate = DATE(YEAR(Sheet1[Initial Receipt Date]), MONTH(Sheet1[Initial Receipt Date]) -3, "1")
EndDate = EOMONTH(Sheet1[Initial Receipt Date], -1)
From there, most likely you can use datesinbetween to get the count:
Count Complaints= var var_Start = MAX(Sheet1[BeginDate]) var var_End = MAX(Sheet1[EndDate]) Return CALCULATE(COUNT(Sheet1[Complaint Metric]), DATESBETWEEN(DateTable[Date], var_Start, var_End))
Please post a workbook with some dummy data so that people can help you out.
HI - thanks so much for your reply - I don't get an option to insert a file. I hope this is enough?
Internal Case Id | Initial Receipt Date | Complaint Metric | Manufacturing Date | Manufacturing Line |
1 | 2/2/2018 | 1 | 10/24/2017 | 1 |
2 | 2/2/2018 | 1 | 12/21/2017 | 1 |
3 | 2/2/2018 | 1 | 12/7/2017 | 1 |
4 | 2/3/2018 | 1 | 12/5/2017 | 1 |
5 | 2/3/2018 | 1 | 1/24/2018 | 1 |
6 | 2/3/2018 | 1 | 12/3/2017 | 1 |
7 | 2/4/2018 | 1 | 1/21/2018 | 1 |
8 | 2/4/2018 | 1 | 1/21/2018 | 1 |
9 | 2/4/2018 | 1 | 12/5/2017 | 1 |
10 | 2/4/2018 | 1 | 11/20/2017 | 2 |
11 | 2/4/2018 | 1 | 12/28/2017 | 2 |
12 | 2/4/2018 | 1 | 7/26/2017 | 2 |
13 | 2/4/2018 | 1 | 5/9/2015 | 2 |
14 | 2/5/2018 | 1 | 8/17/2017 | 2 |
15 | 2/5/2018 | 1 | 1/13/2018 | 2 |
16 | 2/5/2018 | 1 | 1/3/2018 | 2 |
17 | 2/5/2018 | 1 | 1/3/2018 | 3 |
18 | 2/5/2018 | 1 | 12/22/2017 | 3 |
19 | 2/5/2018 | 1 | 1/11/2018 | 3 |
20 | 2/5/2018 | 1 | 12/17/2017 | 3 |
so in this picture - the top visual is complaints received date. bottom visual is by manufactured date.
so when i select complaints received in December (=211), I can see the total productus manufactured in OND was (68+46+11)
I want a table which shows this by month, without having to manually select different filters - i should just see the data by month.
I don't have your sample so I can't really test but, an idea would to harvest the min and max dates:
BeginDate = DATE(YEAR(Sheet1[Initial Receipt Date]), MONTH(Sheet1[Initial Receipt Date]) -3, "1")
EndDate = EOMONTH(Sheet1[Initial Receipt Date], -1)
From there, most likely you can use datesinbetween to get the count:
Count Complaints= var var_Start = MAX(Sheet1[BeginDate]) var var_End = MAX(Sheet1[EndDate]) Return CALCULATE(COUNT(Sheet1[Complaint Metric]), DATESBETWEEN(DateTable[Date], var_Start, var_End))
Hi - i have another question. I have a measure which calcs complaints received and I can show this data in a matrix, per month.
I use the datesbetween to calculate the number of complaints produced last 3 months. I have a calendar table - I have an active relationship with complaint receipt date and an inactive relationship with production date.
thanks so much - in Begindate - I used -2 rather than -3 (this gave me the date range I needed for each month).
otherwise, this works perfectly. I now have the data I need. Thanks so much for your help!!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
82 | |
64 | |
63 | |
56 |
User | Count |
---|---|
171 | |
113 | |
110 | |
73 | |
73 |