The data snapshot has been attached (refer to data_snapshot.png)
Initially, I had data for dates for 2016-2017 and the expression above worked perfectly well.
MIN() converted dates to their StartOfMonth equivalent.
However, when I added dates for the first six months of 2018 (see lower part of data_snapshot.png), only those RegistrationDate values which fall on the first of a given Month were accounted for.
in other words out of about a hundred dates which I added for 2018, only 20 are accounted for in the resulting Pivot Tableresulting_table.png), while for 2016 and 2017 I get an accurate count for all Customers who registered throughout 2016-2017.
Note: StartOfMonth is product of MonthStart("Date") whereas Date is a column in the Calendar table which contains a range of all dates for 2016-2018 (refer to date_snapshot)
Question: how can I correct the SUM() expression above to ensure that all CustomerIDs for all years are accounted for for all RegistrationDate\s and not just the ones who happened to had registered on the first date of the month?
(Refer expected_result.png which was calculated using flags and SUM())
Were the dates for 2018 entered in exactly the same format as the dates for the prior years? Perhaps some the dates are not being recognized as dates, and the condition will return false - counting less than expected.
Check that all the dates are proper numeric values - they will right align in a list box.
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein