DistinctCount with multiple time intelligence filter

Question

Is there a way to reduce my syntax into single Calculate DistinctCount?

My query

CALCULATE( (
    CALCULATE(
            DISTINCTCOUNT('Sales Invoice'[Customer Code]),
            DATESBETWEEN(DimDate[Date], EDATE(MIN(DimDate[Date]), -2), EDATE(MAX(DimDate[Date]), -1))
    ) = 0 
    &&
    CALCULATE(
            DISTINCTCOUNT('Sales Invoice'[Customer Code]),
            DATESBETWEEN(DimDate[Date], EDATE(MIN(DimDate[Date]), -0), EDATE(MAX(DimDate[Date]), -0))
    ) = 1
)

I want to check between multiple time intelligence filters like, Customer who have last record 3 months ago and have no record 2 months ago.

Is there a query something like this??

CALCULATE(
            DISTINCTCOUNT('Sales Invoice'[Customer Code]),
            DATESBETWEEN(DimDate[Date], EDATE(MIN(DimDate[Date]), -2), EDATE(MAX(DimDate[Date]), -1)) = 0 &&
            DATESBETWEEN(DimDate[Date], EDATE(MIN(DimDate[Date]), 0), EDATE(MAX(DimDate[Date]), 0)) = 1
    )

Nick A · Accepted Answer · 2024-09-23 14:18:25Z

0

Try this measure:

Measure = 
VAR thisDate = TODAY()
RETURN
CALCULATE(
            DISTINCTCOUNT('Sales Invoice'[Customer Code]),
            (DimDate[Date] > EDATE(thisDate, -3) && EDATE(thisDate, -1) > DimDate[Date] ) // Include 3 months ago from today's date
            && NOT (DimDate[Date] > EDATE(thisDate, -2) && EDATE(thisDate, -1) > DimDate[Date] ) // Exclude 2 months ago from today's date
)

answered Sep 23, 2024 at 14:18

Nick A

1,1202 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jayz Over a year ago

Thank you for your response @Nick, but it didn't work. By the way I have a 'Sales Invoice'[Date] on my Sales Invoice table.

Nick A Over a year ago

Can you please provide some sample data and what the output of this measure was against your data?

Jayz Over a year ago

Actually this query is working. It just don't give me the value in my grand total.. IF( DISTINCTCOUNT('Sales Invoice'[Customer Code]), DATESBETWEEN(DimDate[Date], EDATE(MIN(DimDate[Date]), -2), EDATE(MAX(DimDate[Date]), -1)) ) = 0 && CALCULATE( DISTINCTCOUNT('Sales Invoice'[Customer Code]), DATESBETWEEN(DimDate[Date], EDATE(MIN(DimDate[Date]), -0), EDATE(MAX(DimDate[Date]), -0)) ) = 1, 1, 0, )

Jayz · Accepted Answer · 2024-09-24 09:02:36Z

0

Finally found the answer.

Customer with Sales 3 months ago but not in the last 2 months =
VAR Last2Months =
CALCULATETABLE (
VALUES ( Sales[CustomerKey] ),
DATESBETWEEN (
Dates[Date],
EDATE ( MIN ( Dates[Date] ), -2 ),
EDATE ( MAX ( Dates[Date] ), 0 )
)
)
VAR _3MonthsAgo =
CALCULATETABLE (
VALUES ( Sales[CustomerKey] ),
DATESBETWEEN (
Dates[Date],
EDATE ( MIN ( Dates[Date] ), -3 ),
EDATE ( MAX ( Dates[Date] ), -3 )
)
)
RETURN
COUNTROWS ( EXCEPT ( _3MonthsAgo, Last2Months ) )

Credit to DAXJutsu

answered Sep 24, 2024 at 9:02

Jayz

13 bronze badges

Collectives™ on Stack Overflow

DistinctCount with multiple time intelligence filter

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related