CODEWITHSUNDEEP
powerbidax
lifeofthenoobie
lifeofthenoobie

Reputation: 169

Count unique occurrences within a year

My database schema looks like down below

ID Date Status
ID1 2022/01/01 Active
ID1 2022/02/01 Active
ID1 2022/03/01 Active
ID1 2022/04/01 Terminated
ID2 2022/01/01 Active
ID2 2022/02/01 Terminated

I'd like to calculate unique occurrences from start of selected date year, till the selected date. My formula is:

CountOfUnique = CALCULATE( DISTINCTCOUNT( 'Table'[ID] ) , 'Table'[STATUS] = "Active", DATESBETWEEN('CALENDAR'[DATE], STARTOFYEAR('CALENDAR'[DATE]), MAX('CALENDAR'[DATE]) ))

In SQL I'd need something like

SELECT COUNT ( DISTINCT ID) FROM Table
WHERE STATUS = "ACTIVE"
AND DATE BETWEEN 2022/01/01 AND 2022/04/01

Upvotes: 0

Views: 191

Answers (2)

Umut K
Umut K

Reputation: 1388

when you have a slicer on the visual, the start of selected date year doesnt mean much as you select the dates on the slicer. I created a Calendar Table = CALENDARAUTO() so it started from the 2022/01/01...

use one of these as you like...

sample PBix File

Unique Count =
VAR _max =
    MAX ( 'Calendar Table'[Date] )
VAR _min =
    MIN ( 'Calendar Table'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[ID ] ),
        'Table'[Date ] <= _max
            && 'Table'[Date ] >= _min
    )

or only Active if you need

Unique Count (Active) =
VAR _max =
    MAX ( 'Calendar Table'[Date] )
VAR _min =
    MIN ( 'Calendar Table'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[ID ] ),
        'Table'[Date ] <= _max
            && 'Table'[Date ] >= _min
            && 'Table'[Status] = "Active"
    )

date relationship

status

Upvotes: 0

Ozan Sen
Ozan Sen

Reputation: 2615

Try this:

CountOfUnique =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[ID] ),
    'Table'[STATUS] = "Active",
    DATESBETWEEN (
        'CALENDAR'[DATE],
        STARTOFYEAR ( 'CALENDAR'[DATE] ),
        SELECTEDVALUE ( 'CALENDAR'[DATE] )
    )
)

Upvotes: 1

Related Questions