Retention rate month to date

[AnjaliBJ]

Hi all,

I have some issues with getting the right result. I am trying to calculate the retention rate month to date.

As you can see below, the second column represents # customers (‘Aantal bedrijven’). I have created a measure where I only need the value of January (since this is the starting point). I filtered this table on this year, but also for the year 2020 I need the same: the value of January for every month of the year (month to date). The DAX formula I created is:
Startpunt aantal debiteurnummers januari =
var jaar = MAX(Datum[year])
return
CALCULATE([Aantal bedrijven]; FILTER(ALL(Datum);Datum[year] = jaar && Datum[month] = 1))

The problem is that it is also showing values for the month November and December and this shouldn’t be the case of course. I don’t know how to adjust the formula so I can get it till month to date. I tried several things.

Second, the companies can cancel their contract, so there is the column ‘opzeggingen per maand’ which contains # cancellations. Next to this column is ‘Opzeggingen cumulatief’ which I created with the following DAX formula:
Opzeggingen cumulatief =
var test = MAX(Datum[Date])
var jaar = MAX(Datum[year])
return
CALCULATE([Opzeggingen per maand];FILTER(ALL(Datum);(DATEADD(Datum[date];1;MONTH) <= test && Datum[year]= jaar))  )

Also here is the case that I want it till month to date

The same for the column ‘Retentie %’. The Dax formula that I created:
Retentie % = 1 + DIVIDE([Aantal debiteurnummers] - [Startpunt aantal debiteurnummers januari]; [Startpunt aantal debiteurnummers januari])

Can someone tell me how to adjust these two formula’s/ or another way to get the retention rate month to date? Thanks in advance!

[bpsearle]

Hey

Can I make a suggestion that might help with getting replies from the form?

It sounds like this can be broken down into smaller separate bite size problems and posted individually. If you can include a screen shot of a mock-up of what you want and the data tables in question that would be very helpful.

This will make it easier to absorb each problem and focus on an answer. Also, you are more likely to get multiple people helping you across the posts so get a faster response.

Thanks, Brian

[AnjaliBJ]

Thank you. I will do that in the future when I will post again!