Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All! I'm stuck with this question and no resource seems to help.
My dataset includes monthly revenues for multiple customers for many years (customer name, invoice date, revenue etc as fields). Some customers have data for all the period some don't. For example if a customer joined in Jan 2023, then there are only 3 invoices in the dataset as of today. Or if a customer customer joined in January 2022 and churned away in December 2022, then there are only 12 data points (rows) in the dataset for this customer.
I want to create a measure that sums up all cases where customers had an INCREASE in revenue (call it Upsell) based on a selected month (slicer with the date field from the dimension table). Another measure would sum up all cases where a customer had a DECREASE in revenue (call it Downsell). Yet another measure should sum up revenues from new business by checking if a customer had no data point in a given month but had a positive revenue in the selected month (New Business). Finally, Churn value would sum up all cases where the customer had -100% downsell (i.e. this should be separate from Downsell).
I have created measures with PARALLELLPERIOD which work with row context - i.e. a table where 1 column is the customer name, this measure can calculate individual month-over-month development. But I'm struggling to create a measure which will work without row context, lets say on a card visual.
@Agshin , What I do in such case that I create a date from month year.
example
Date = datevalue( "01-" &[Month Name] & "-" [year] )
Date = date([year], month no], 1)
Now I create a date table and join it with this date. And Now I can use all time intelligenec functions
These customer churn techinuques can help
Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q
Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...
Calendar = Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "FY Year", if( Month(_max) <7 , year(_max)-1 ,year(_max))
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)
User | Count |
---|---|
94 | |
86 | |
78 | |
70 | |
63 |
User | Count |
---|---|
114 | |
101 | |
97 | |
66 | |
59 |