DAX rolling churn for the month (MTD)
Hi guys, I’d like to ask for your assistance in getting the correct output. The goal is to calculate a rolling daily count of churned services. Appreciate your help in advance. SUMMARIZE( ADDCOLUMNS( churn_services, "Date", churn_services[disconnectiondate], "Customer Type", churn_services[customertypename], "Measures", "MTD Churn", "Product Category", churn_services[productcategoryname], "UB Group", churn_services[groupname], "Total Churn Services", CALCULATE( DISTINCTCOUNT(churn_services[serviceid]), FILTER( ALLEXCEPT( churn_services, churn_services[customertypename], churn_services[productcategoryname], churn_services[productitemname], churn_services[groupname] ), churn_services[disconnectiondate] <= MAX(churn_services[disconnectiondate]) && MONTH(churn_services[disconnectiondate]) = MONTH(TODAY()) && YEAR(churn_services[disconnectiondate]) = YEAR(TODAY()) ) ) ), [Date], [Measures], [Customer Type],Running Total and shifting the date back by one month.
Hi, I’d like to seek your help to fix my issue in DAX measure with calculating the Running total with shifting date back by month. This is the DAX code for Running total (BX column) MEASURE_Services_at_Start_of_Period = CALCULATE( DISTINCTCOUNT(crm[serviceid]), FILTER( ALLEXCEPT( crm, crm[groupname], LKP_group[Group], crm[productcategoryname], LKP_productcategory[Product Category], crm[customertypename], crm[disconnectiondate], LKP_calendar[Date] ), crm[activationdate] <= MAX(crm[activationdate]) ) ) And this is the DAX code for Running total, but shifting the date back by month (BY column) MEASURE_Services_at_Start_of_Period_Last_Month = VAR temp1 = CALCULATE( DISTINCTCOUNT(crm[serviceid]), DATEADD(LKP_calendar[Date], -1, MONTH)) VAR temp2 = CALCULATE( temp1, FILTER( ALLEXCEPT( crm, crm[groupname], LKP_ubgroup[UB Group], crm[productcategoryname], LKP_productcategory[Product Category], crm[customertypename], crm[disconnectiondate], LKP_calendar[Date] ), crm[activationdate] <= MAX(crm[ubactivationdate]) ) ) RETURN temp2 However, this is the outcome I received.
Current day Ending balance should show up as Starting balance for next day
Hello Folks, Need your help to figure out this interesting qusetion. How to write a DAX expression/measure to show currrent day ending balance as a Starting balance for next day and continue addin up as shown in the example below: Any ideas? Thanks in advance.List of values with filter, kindly help me!
Hi Team, Kindly help me for filter visual. I have the 'Data' column and categories columns. like below snapshot format. we required filter, If I select categories col of 'a' then "a" related all data shown like below snapshot. If select 'b' then "b" related of all data required. How to it in DAX with this? Note:- In power query it is possible, by splitting muliple cols and we will do it. but have performacne impact, kindly help me to it using DAX?calculate daily target achievements
Hello all, Please I need support relating a calculation I need to add in my dashboard refering to the achievement based on family products. I have in the below table the daily sales revenues and in the last 3 columns the monthly target and based on these data I need to calculate the daily achievement vs target in % as well the remaining revenue vs target. Sales name beyound Connectivity daily sales fixed daily sales Mobility daily sales Beyound connectivity monthly target Fixed monthly target Mobility monthly target Anela 1500 0 0 78000 117500 40000 Elma 0 0 1500 90000 112500 38000 Ornel 1000 0 15417 75000 95000 38000 Xhuljana 0 1032 22750 75000 95000 38000 Total 4333 1032 42142 412000 535000 194000 Goga Team contains the Target revenues based on family and Ervis Goga monthly revenues contain the daily sales revenues. Can you help me please? Thank you!
Calculating Remaining Active Joiners After Joining Month in Power BI
Description: Hello Power BI Community, I'm seeking help with creating a DAX measure in Power BI to calculate the number of remaining active joiners each month after their joining month. I have two main tables: JoinersSummary and NewJoinerAndLeavers, both connected to a Calender table. Objective: Determine the number of remaining active joiners in each subsequent month after they initially joined. Data Structure: JoinersSummary table contains columns: SimNo, IsNewJoiner, Merged (joining date in "yyyy-mm" format). JoinerAndLeavers table contains columns: SimNo, Merged, ThreeMonthLeaver AND MORE Current Issue: I have created a measure to calculate remaining joiners, but it currently returns the same count as the new joiners, rather than the count of those who are still active after the joining month. The goal is to have a measure that correctly identifies and counts the remaining active joiners for each month. Remaining Joiners Optimized = VAR SelectedMonth = MAX('JoinersSummary'[Merged]) VAR JoinersInSelectedMonth = CALCULATETABLE( VALUES('JoinersSummary'[SimNo]), 'JoinersSummary'[IsNewJoiner] = 1, 'JoinersSummary'[Merged] = SelectedMonth, REMOVEFILTERS('NewJoinerAndLeavers') ) VAR RemainingJoinersCount = CALCULATE( DISTINCTCOUNT('NewJoinerAndLeavers'[SimNo]), 'NewJoinerAndLeavers'[SimNo] IN JoinersInSelectedMonth, 'NewJoinerAndLeavers'[Merged] > SelectedMonth, REMOVEFILTERS('Calender') ) RETURN RemainingJoinersCount This measure returns the same count as the new joiners count, not reflecting the actual number of joiners remaining active after the joining month. Additional Information: The JoinerSummary table . is made up of data from the NewJoinersandLeavers Table The Merged column is in the "yyyy-mm" format as a Date type. Request: I need guidance on correctly calculating the remaining active joiners for each subsequent month after their joining month. Any insights or suggestions would be greatly appreciated. Thank you for your help!DAX query required for IF and IF Else condition of below case
Hi Team, I am new to PBI, help me with this case! IF type(col) = "AAA" & Date time(col) < 11AM then Pickup date = Date(col)-1 ELSE Pickup date = Date(col) IF type(col) = "BBB" then Pickup date = Date(col)–1 IF type(col) = "CCC" then Pickup date = Date(col) Value of "AAA" is having AAA_a, AAA_b, AAA_c, AAA_d, AAA_e ----AAA_n etc. So here we are calling the condition with "AAA". How do we calling "AAA" with one IF condition statement or any other. Same like value of "BBB" & Value of "CCC". Here date time(col) contains 24hrs, we required less than(< )upto 11AM then Pickup date = Date(col)-1, else Pickup date = Date(col). Here Date(col) is calculated column. So, kindly helpe me with the DAX query. below is the snapshot for your reference.
Open Incidents per day
Dear All, I'm having some trouble with creating a measure which check for the number of open incidents. I want to place this measure in a matrix or chart with Date on the rows. My thought process has been the following: I have a fact table called Raw_Transaction which contains status changes for incidents such as creating, updating, and closing. An IncidentId can appear multiple times in this table, and an incident ID can be opened and closed multiple times. So an incident ID will have multiple records. This table is ~15.000.000 rows. To make it a bit easier for myself (I think...), I've created a column which is a basically a RANK number of what number this transaction is for an incidentID, called "CountOfTransaction". E.g. incidentID = 1 and CountOftransaction = 6, that means it's the 6th status change/transaction for incidentID 1. To get each incidents latest status for a date, my idea was to: Create vTable with CALCULATETABLE Filter this table using DATESINPERIOD to adjust the daterange when the measure is in a visual. Then Create a column with ADDCOLLUMS which checks for each incident ID's the highest "CountOftransaction" in the context that has been adjusted by DATESINPERIOD. FILTER that in a ROW context to say that the CountOFtransaction needs to be equal the [@MaxTransaction] Count the IncidentID's where that result is not "Closed". Currently I have the expression below (hope this illustrates what I want to accomplish) but it’s not working as I'd hoped. Would it be possible to comment on how to create the correct calculation? If you can comment on 'why' it's not working that be appreciated as well! Thanks in advance for the assistance! Open Incidents = VAR MaxDate = MAX('_Date'[Date]) VAR MinDate = CALCULATE( MIN( '_Date'[Date] ), REMOVEFILTERS() ) VAR NoOFDays = ABS(Maxdate) - ABS(MinDate) VAR Period = DATESINPERIOD( _Date[Date], Maxdate, -NoOFDays, DAY ) VAR _CalcTable = CALCULATETABLE( Raw_Transactions, Period ) VAR _vTable = ADDCOLUMNS( _CalcTable, "@MaxTransaction", VAR Result = CALCULATE( MAX( Raw_Transactions[CountOfTransaction] ), ALLEXCEPT( Raw_Transactions, Raw_Transactions[IncidentId] ) ) RETURN IF( Result = Raw_Transactions[CountOfTransaction], Result ) ) RETURN CALCULATE( COUNTX( _vTable, [@MaxTransaction] ), Raw_Transactions[TransactionType] <> "Closed" )
