Forum Discussion

karthikponnaih's avatar
karthikponnaih
Frequent Visitor
6 years ago

Calculate Monthly Retention Performance

Hi   I am new to PowerBI and trying to create a POC for our business requirements. Attached the dataset and information around the data. Could someone help me in find the monthly retention perfo...
Need Help
v-alq-msft's avatar
v-alq-msft
Community Support
6 years ago

Hi, karthikponnaih 

 

I wonder the definition of monthly retention performance. If you want to count the number of records where the current month-year is between the start date and the end date and the product name is 'flex', you may try the following steps.

 

Table:

 

Calendar:

 

Calendar = CALENDARAUTO()

 

 

You may create a calculated column and a measure as below.

 

Calculated column:
Month-Year = VALUE(FORMAT('Calendar'[Date],"yyyymm"))

Measure:
Result = 
var _monthyear = SELECTEDVALUE('Calendar'[Month-Year])
var tab = 
ADDCOLUMNS(
    ALLSELECTED('Table'),
    "startmonthyear",
    VALUE(FORMAT('Table'[effective_start_date],"yyyymm")),
    "endmonthyear",
    VALUE(FORMAT('Table'[effective_end_date],"yyyymm"))
)
var newtab = 
ADDCOLUMNS(
    tab,
    "flag",
    IF(
       _monthyear>=[startmonthyear]&&
       _monthyear<=[endmonthyear],
       1,0
    )
)

return
COUNTROWS(
    FILTER(
        newtab,
        [flag] = 1&&
        [product_name] = "flex"
    )
)

 

 

Result:

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.