difference in distinct count of a column between 2 dates

[Anonymous]

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:

DateStart	Datestart(Month-1)	ClientGrouping	currentClientKey(distinctcount)	PreviousClientKey(distinctcount as at 1 month prior)	Difference between current and previous counts of clientkeys
2019-12	2019-11	GroupA	5000	4000	-1000
2019-12	2019-11	GroupB	3000	3500	+500
2019-12	2019-11	GroupC	500	800	+300
2020-01	2019-12	GroupA	4000	3500	-500
2020-01	2019-12	GroupB	3500	5500	+2000
2020-01	2019-12	GroupC	1000	900	-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

[v-juanli-msft · Accepted solution]

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]

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.