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 to 202312. And the results should be 98 customers but still get 47..I remove the slicer filter and add DATESINPERIOD but do not work.

 

 

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
                DATESBETWEEN(
                    DateTable[Date],
                    DATE(YEAR(MAX(DateTable[Date])), MONTH(MAX(DateTable[Date])) - 11, 1),  -- Start of the period
                    EOMONTH(MAX(DateTable[Date]), 0)  -- End of the period
                )
            )
        ),
        [CustomerVolume] > 0
    )
RETURN
COUNTROWS(AuxTable)


 
How can I fix it? I am using this code, because I want to after add some code with Volume is higher or lower than average.
Need Help

4 Replies

  • vojtechsima's avatar
    vojtechsima
    Super 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.

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    Hi japanfelipe ,

    Please use this refined measure to achieve your goal:

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