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
vojtechsima's avatar
vojtechsima
Icon for Super User rankSuper User
1 year ago

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)
)
  • japanfelipe's avatar
    japanfelipe
    Frequent Visitor
    1 year ago

    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.