topic Calculating Removed Users based on previous month totals in DAX Commands and Tips

Calculating Removed Users based on previous month totals

carollyna205 — Tue, 18 Jul 2023 13:10:24 GMT

I have table like this, I want to create column removed users which will calculate how many users have been removed in new month, so for example firs one will be: Count of Users (from January) + Count of New Users (from February) - Count of Users (February) = removed users in February

I have tried this: RemovedUsers = CALCULATETABLE((('Dashboard_data_2023'[GPN DCOUNT]),DATESMTD(dateadd('Dashboard_data_2023'[WEEK],-1,MONTH)))+'Dashboard_data_2023'[Count of GPN for NEW]-CALCULATE(('Dashboard_data_2023'[GPN DCOUNT]),DATESMTD('Dashboard_data_2023'[WEEK])))

but it creating column with the same values as column Count of New Users

Re: Calculating Removed Users based on previous month totals

lbendlin — Thu, 20 Jul 2023 00:48:23 GMT

According to your sample data "Count of Users" is the sum of "Count of Continue Users" and "Count of New Users". That means your formula

Count of Users (from January) + Count of New Users (from February) - Count of Users (February)

can be rewritten as

Count of Users (from January) - Count of Continue Users (February)

Next you need an index column (or at least convert your date text into a proper date). That will then allow you to do the calculation. You can decide if this should be a measure, a calculated column, or a Power Query code.

Re: Calculating Removed Users based on previous month totals

carollyna205 — Thu, 20 Jul 2023 08:04:55 GMT

It can not be rewritten this way and the total will show me sum of new and removed user. and I need only removed user

I know what logic I need, what I am looking for here is how to write dax for it as I need to do this automatically for every new month which will show there not manually doing calculations for each month