Forum Discussion
Issues with count distinct when using DATESINPERIOD
I have a table Invoice with conection with Date Table. I have 47 customers that bought in the slicer select YearMonth 202312... But I have a report that calculate the 12 months back so from 202301 ...
Hi, japanfelipe
I don't see where you added DATESINPERIOD. I only see DATESBETWEEN, which could prolly do the same, however, the DATESINPERIOD can do all heavy lifting for you.
So technically you could do:
CALCULATE(
SUM(Invoice[Volume]),
DATESINPERIOD(DateTable[Date], MAX(DateTable[Date]), -12, MONTH)
)
Yes I could.. I have this code.
Test Total CustCount 1 =
CALCULATE(
DISTINCTCOUNT(Invoice[Customer Code]), -- Count distinct customer codes
REMOVEFILTERS(DateTable[Year Month Sort]), -- Remove slicer filters
DATESBETWEEN(
DateTable[Date],
DATE(YEAR(MAX(DateTable[Date])), MONTH(MAX(DateTable[Date])) - 11, 1), -- First day of the earliest month
EOMONTH(MAX(DateTable[Date]), 0) -- Last day of the latest month
)
)
But I want to compare the Volume of the customer if is Higher than AVERAGE and classify and COUNT..
So I need to count customers where volume is >= AVERAGE and less. And use other comparison as well.