Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I need to build a bar chart that will count how many batches have goods receipt date for a given month. The data set is actual inventory table showing on-hand inventory per batch for the end of the month. Some batches have goods receipt date during the current month and some - from the previous months. We need to calculate the number of batches that were receipted during the current month.
The same of the data looks like this:
Date of submission | GR Date | Batch Number |
01.01.2024 | 15.11.2023 | 1 |
01.01.2024 | 18.11.2023 | 2 |
01.01.2024 | 06.12.2023 | 3 |
01.01.2024 | 08.12.2023 | 4 |
01.01.2024 | 02.01.2024 | 5 |
01.01.2024 | 03.01.2024 | 6 |
01.01.2024 | 05.01.2024 | 7 |
01.01.2024 | 10.01.2024 | 8 |
01.01.2024 | 15.01.2024 | 9 |
01.01.2024 | 16.01.2024 | 10 |
01.01.2024 | 17.01.2024 | 11 |
01.02.2024 | 18.11.2023 | 2 |
01.02.2024 | 08.12.2023 | 4 |
01.02.2024 | 03.01.2024 | 6 |
01.02.2024 | 05.01.2024 | 7 |
01.02.2024 | 16.01.2024 | 10 |
01.02.2024 | 17.01.2024 | 11 |
01.02.2024 | 02.02.2024 | 12 |
01.02.2024 | 05.02.2024 | 13 |
01.02.2024 | 14.02.2024 | 14 |
01.02.2024 | 25.02.2024 | 15 |
01.03.2024 | 08.12.2023 | 4 |
01.03.2024 | 03.01.2024 | 6 |
01.03.2024 | 25.02.2024 | 15 |
01.03.2024 | 06.03.2024 | 16 |
01.03.2024 | 11.03.2024 | 17 |
01.03.2024 | 18.03.2024 | 18 |
01.03.2024 | 25.03.2024 | 19 |
I have tried to solve it in several ways, but without success so far:
- using set expression: Count(distinct{<[Date of submission.autoCalendar.YearMonth]={"[GR Date.autoCalendar.YearMonth]"}>}[Batch Number])
- variable in the load script: [Batch Receipted During Month of Submission] = if(([GR Date.autoCalendar.YearMonth]=[Date of submission.autoCalendar.YearMonth]),'Yes','No') and then using it in the bar chart measure: Count(distinct{<[Batch Receipted During Month of Submission]={"Yes"}>}[Batch Number])
- master measure Receipted batch v.3: if(([GR Date.autoCalendar.YearMonth]=[Date of submission.autoCalendar.YearMonth]),'Yes','No') and then using it in the bar chart measure: Count(distinct{<[Receipted batch v.3]={'Yes'}>}[Batch Number])
Many thanks in advance!
Your second option looks like the best way to go, but I would try to explicitly add the monthyear fields in the load editor rather than using the autocalendar. Something like this appears to work for me:
[Data]:
LOAD
*,
if(([GR Month]=[Month of submission]),'Yes','No') as [Batch Receipted During Month of Submission]
;
LOAD
Date(Date#([Date of submission], 'MM.DD.YYYY') ) AS [Date of submission],
Date(Date#([GR Date], 'DD.MM.YYYY') ) AS [GR Date],
Date(MonthStart(Date#([Date of submission], 'MM.DD.YYYY')), 'YYYYMM') AS [Month of submission],
Date(MonthStart(Date#([GR Date], 'DD.MM.YYYY')), 'YYYYMM') AS [GR Month],
[Batch Number]
FROM [lib://AttachedFiles/test.xlsx]
(ooxml, embedded labels, table is Sheet1);
Then creating the bar chart as you described
Your second option looks like the best way to go, but I would try to explicitly add the monthyear fields in the load editor rather than using the autocalendar. Something like this appears to work for me:
[Data]:
LOAD
*,
if(([GR Month]=[Month of submission]),'Yes','No') as [Batch Receipted During Month of Submission]
;
LOAD
Date(Date#([Date of submission], 'MM.DD.YYYY') ) AS [Date of submission],
Date(Date#([GR Date], 'DD.MM.YYYY') ) AS [GR Date],
Date(MonthStart(Date#([Date of submission], 'MM.DD.YYYY')), 'YYYYMM') AS [Month of submission],
Date(MonthStart(Date#([GR Date], 'DD.MM.YYYY')), 'YYYYMM') AS [GR Month],
[Batch Number]
FROM [lib://AttachedFiles/test.xlsx]
(ooxml, embedded labels, table is Sheet1);
Then creating the bar chart as you described
It worked perfectly! Thanks a lot!!!