Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi!
I have the dataset attached, and want to have the following output:
monthly, the total quantity of customers (id-cliente), categorized in:
Example of the desired output:
Any ideas of how to do it?
Tks!!
Solved! Go to Solution.
Hi @gtrani ,
According to your description, I download your dataset and here's my solution.
Create two calculated columns.
YearMonth =
YEAR ( [Data] ) & "-"
& IF ( LEN ( MONTH ( [Data] ) ) = 1, 0 & "" & MONTH ( [Data] ), MONTH ( [Data] ) )
Category =
VAR _New =
COUNTROWS (
FILTER (
'Planilha1',
'Planilha1'[id-cliente] = EARLIER ( 'Planilha1'[id-cliente] )
&& 'Planilha1'[YearMonth] < EARLIER ( 'Planilha1'[YearMonth] )
)
)
VAR _LastM =
COUNTROWS (
FILTER (
'Planilha1',
'Planilha1'[id-cliente] = EARLIER ( 'Planilha1'[id-cliente] )
&& EOMONTH ( 'Planilha1'[Data], 0 ) = EOMONTH ( EARLIER ( Planilha1[Data] ), -1 )
)
)
RETURN
IF ( _New = 0, "New", IF ( _LastM > 0, "LastM", "Before" ) )
Put YearMonth column in X-axis, Category in Y-axis and Legend, Get the result.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @gtrani ,
According to your description, I download your dataset and here's my solution.
Create two calculated columns.
YearMonth =
YEAR ( [Data] ) & "-"
& IF ( LEN ( MONTH ( [Data] ) ) = 1, 0 & "" & MONTH ( [Data] ), MONTH ( [Data] ) )
Category =
VAR _New =
COUNTROWS (
FILTER (
'Planilha1',
'Planilha1'[id-cliente] = EARLIER ( 'Planilha1'[id-cliente] )
&& 'Planilha1'[YearMonth] < EARLIER ( 'Planilha1'[YearMonth] )
)
)
VAR _LastM =
COUNTROWS (
FILTER (
'Planilha1',
'Planilha1'[id-cliente] = EARLIER ( 'Planilha1'[id-cliente] )
&& EOMONTH ( 'Planilha1'[Data], 0 ) = EOMONTH ( EARLIER ( Planilha1[Data] ), -1 )
)
)
RETURN
IF ( _New = 0, "New", IF ( _LastM > 0, "LastM", "Before" ) )
Put YearMonth column in X-axis, Category in Y-axis and Legend, Get the result.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
96 | |
90 | |
79 | |
67 |
User | Count |
---|---|
150 | |
118 | |
111 | |
106 | |
95 |