Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |