Count customers per Business Unit in a Subscription based model
Trying to solve a measure for a subscription based business. The measure should calculate number of customers per month. I work with a dataset that stores all the changes on the subscription. I can therefore have man rows per subscription. The measure works fine except when a subscription is moved between Business units. The customer then counts on both buinsess units instead of just the correct one. The measure also counts correct when looking at the whole business. I tried adding All(Business units) in both MaxPricedates and Results without effect. Measure: Number customers = VAR MaxDate = MAX ( Date[Date] ) VAR FromDate = ENDOFMONTH(Date[Date]) VAR EndDate = ENDOFMONTH('Subscriptions'[ActualDate]) VAR EndOfLifeDate = STARTOFMONTH(Date[Date]) VAR MaxPrisDates = CALCULATETABLE ( ADDCOLUMNS ( SUMMARIZE ('Subscriptions','Subscriptions'[SubscriptionID]), "@MaxBalanceDate", CALCULATE (Max('Subscriptions'[ActualDate])) ), Date[Date] <= MaxDate ) VAR MaxPrisDatesWithLineage = TREATAS ( MaxPrisDates, 'Subscriptions'[SubscriptionID], Date[Date]) VAR Result = CALCULATE( DISTINCTCOUNT( 'Subscriptions'[AccountID]), MaxPrisDatesWithLineage, 'Subscriptions'[Start date] <= FromDate, 'Subscriptions'[End date] >= EndOfLifeDate, 'Subscriptions'[Status] <> "Other", 'Subscriptions'[Status] <> "Stopped" ) RETURN Result Grateful for assistansBypass an explicit filter to have a global total
Hi, I have a view where I show a total of spendings per month. Then I divide this total in four categories in the purpose of separate spendings less than, equal to (not remarquable), without comparision with or greater than the previous month. My objective is to show uniquely the values where the variation to the previous month is greater than 1% of the total of the current month. For instance, the first line of the category (Inferiores mas de 10% al mes anterior) is correct because the variation is greater than the total (30 970 000 / 100 = 309 700) but the fourth one is not supposed to appear. ((616 000 - 501 000) < 309 700) My measure for the total global is this : Mes actual general = CALCULATE([Mes actual], ALLSELECTED('Facturas Proveedores')) My issue is that when I use my measure "Total mes actual" which is supposed to be global, I have the total of the filtered category. I know that it is the normal behaviour of ALLSELECTED because we cannot remove the explicit filters however I would like to find a way to get this global value, either with DAX or modeling or any proposition you can make. Thanks !
Need Dax Help Please!
Hi Guys, I have a table with a task code column and a count column. Im trying to add a calculated column to give me the desired result below. I need tasks 51E and 51F to be added together but the others just what they are. Desired Result: Task Code Count Desired Calculated Column 6A 42 42 7B 24 24 51D 20 20 51E 60 100 51F 40 100 Thanks!
