Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

difference in distinct count of a column between 2 dates

Greetings

 

If this has been answered elsewhere, kindly provide the link as I have been unsuccessful in locating something of the sort.

Our company has recently switched over to PowerBI and I'm very new to it.  My problem is as follows:

 

I have a dataset with, among others, date values (YYYY-MM-dd) a clientkey and a clientgrouping (none of which are unique, if that matters) that gets imported from a SQL server.

I need to return a table that displays, by clientgrouping, a distinct count of clientkeys for the date (rolled up to month), previous month, as well as the difference in distinctcount of clientkeys compared to a month ago.

 

Entrydata is in the following format:

Datestart

ClientGrouping

ClientKey

2019-12-07

GroupB

705447510393

2019-12-07

GroupD

756110728059

2019-12-09

GroupA

901375833853

2019-12-09

GroupB

674050068801

2019-12-09

GroupD

590188068342

2019-12-22

GroupC

615359277368

2020-01-05

GroupB

272565905672

2020-01-05

GroupC

681092857990

2020-01-19

GroupD

447412329611

 

 

Desired result as follows:

DateStartDatestart(Month-1)ClientGroupingcurrentClientKey(distinctcount)PreviousClientKey(distinctcount as at 1 month prior)Difference between current and previous counts of clientkeys
2019-122019-11GroupA5000
4000
-1000
2019-122019-11GroupB30003500+500
2019-122019-11GroupC500800+300
2020-012019-12GroupA40003500-500
2020-012019-12GroupB35005500+2000
2020-012019-12GroupC1000900-100

 

I have played around with some quick measures and started reading up on DAX but have naught but numerous sheets filled with failures to show for my efforts, Nothing has even come close enough to warrant mentioning.

 

The fields in black are simply rows and columns from my dataset.

"Datestart(Month-1)" needs to calculate 1 month prior to the DateStart field 

"PreviousClientKey(distinctcount as at 1 month prior)" needs to do a distinctcount of the clientkey where the datestart matches 1 month earlier as shown in the graphic.

"Difference between current and previous counts of clientkeys" needs to calculate and show the difference between the 2 aforementioned values.

 

 

I hope this explanation is clear enough and would be very appreciative of any help in this regard.

 

Thanks in advance

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

Create a column

year-month = FORMAT([datestart],"yyyy-mm")

Create measures

discount current =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[clientkey] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[clientgrouping]
            = MAX ( 'Table'[clientgrouping] )
            && 'Table'[year-month]
                = MAX ( 'Table'[year-month] )
    )
)

discount month-1 =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[clientkey] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[clientgrouping]
            = MAX ( 'Table'[clientgrouping] )
            && DATEDIFF (
                'Table'[datestart],
                MAX ( 'Table'[datestart] ),
                MONTH
            ) = 1
    )
)


difference = [discount current]-[discount month-1]

Capture1.JPG

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

Create a column

year-month = FORMAT([datestart],"yyyy-mm")

Create measures

discount current =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[clientkey] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[clientgrouping]
            = MAX ( 'Table'[clientgrouping] )
            && 'Table'[year-month]
                = MAX ( 'Table'[year-month] )
    )
)

discount month-1 =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[clientkey] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[clientgrouping]
            = MAX ( 'Table'[clientgrouping] )
            && DATEDIFF (
                'Table'[datestart],
                MAX ( 'Table'[datestart] ),
                MONTH
            ) = 1
    )
)


difference = [discount current]-[discount month-1]

Capture1.JPG

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.