Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi community,
In the following dataset, I want to create a measure to calculate : 'for clients signed up 3 months ago, how many made payment in next two months'. Then calculate the monthly average over these next two months.
for instance, in following snippet if 22 clients signed up 3 months ago( in Apr), out of 22, 11 made a payment in May & 15 made a payment in June, then monthly average over May & June: (11+15)/(22x2)=59%
Here is my sample pbi file:
https://1drv.ms/u/s!Ag919_pO_UKrghhIKgRk6OM2HdLS?e=HB1m2N
Many thanks for your help!
Solved! Go to Solution.
Hi,
Please try using the below measure.
client count within two months ratio: =
VAR _twomonthlaterendofmonth =
EOMONTH ( MAX ( Datedim[Date] ), 2 )
VAR _customerlist =
FILTER (
SUMMARIZE ( 'Table', 'Table'[Client_id], 'Table'[paid_date] ),
'Table'[paid_date] <= _twomonthlaterendofmonth
)
RETURN
DIVIDE ( COUNTROWS ( _customerlist ), [Signups] * 2 )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Please try using the below measure.
client count within two months ratio: =
VAR _twomonthlaterendofmonth =
EOMONTH ( MAX ( Datedim[Date] ), 2 )
VAR _customerlist =
FILTER (
SUMMARIZE ( 'Table', 'Table'[Client_id], 'Table'[paid_date] ),
'Table'[paid_date] <= _twomonthlaterendofmonth
)
RETURN
DIVIDE ( COUNTROWS ( _customerlist ), [Signups] * 2 )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
User | Count |
---|---|
103 | |
87 | |
77 | |
70 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |