Count batches with goods receipt date within a specific month

[Nickolay1]

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!

[BenjaminT · Accepted solution]

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

[Nickolay1]

It worked perfectly! Thanks a lot!!!