Forum Discussion

japanfelipe's avatar
japanfelipe
Frequent Visitor
1 year ago

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 ...
Need Help
japanfelipe's avatar
japanfelipe
Frequent Visitor
1 year ago

Did not work this code.

 

Test Total CustCount =
VAR AuxTable =
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE(
                Invoice,
                Invoice[Customer Code]
            ),
            "CustomerVolume",
            CALCULATE(
                SUM(Invoice[Volume]),
                REMOVEFILTERS(DateTable),  -- Remove all filters from the DateTable
                DATESINPERIOD(
                    DateTable[Date],
                    MAX(DateTable[Date]),  -- Use the max date in context
                    -12,                   -- Look back 12 months
                    MONTH
                )
            )
        ),
        [CustomerVolume] > 0
    )
RETURN
COUNTROWS(AuxTable)

I have this other that works, but I want to use FILTER(ADDCOLUMNS to later on classifify the customers based on Volume >= AVERAGE VOLUME and Number of distinct YearMonth >= 6.

 

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
    )
)