Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
I have a table as below.
customer | status | Month |
aaaa | active | 3 |
bbbb | inactive | 3 |
cccc | active | 3 |
bbbb | active | 4 |
cccc | inactive | 4 |
aaaa | inactive | 4 |
aaaa | active | 5 |
bbbb | active | 5 |
eeee | active | 5 |
I am combining all month tables and want to see descripiences between months with using month slicer on report page.
for example for new customers, on report page when I choose 5th month as slicer, I want to see difference between previous month. In this example I need to see aaaa and eeee as new (even aaaa was in the list but it was inactive). for comparing 2 months before, I need to see bbbb and eeee as same logic. also I need to show their counts like 2 for both example.
I want to make some addition in my request. actually I want to see that how many new customers added comparison with last month 3 months and 12 months. and also how many customer we lost. and who are they.
Solved! Go to Solution.
solved the issue with replacing measure and use it as column formula and remove some parts and made it with 2 columns. also it can be merge but i want to be sure that formulas are working.
lastmonth =
VAR vCustomerRow = Customer[customer]
VAR vMonthRow = Customer[Month]
RETURN
CALCULATE (
MAX ( Customer[status] ),
Customer[customer] = vCustomerRow,
Customer[Month] = vMonthRow - 1,
ALL ( Customer )
)
countnew -customer = IF(Customer[lastmonth]=BLANK()&&Customer[status]="Active",1,IF(Customer[lastmonth]<>Customer[status]&&Customer[status]="Active",1,IF(Customer[lastmonth]=Customer[status]&&Customer[status]="Active", BLANK(), 0)))
_ =
VAR __t =
TOPN(
1,
CALCULATETABLE(
CUST,
ALLEXCEPT( CUST, CUST[customer] ),
SLC[Mth] < MAX( SLC[Mth] )
),
CUST[Month]
)
RETURN
IF(
MAX( CUST[status] ) = "active"
&& (
ISEMPTY( __t )
|| MAXX( __t, CUST[status] ) = "inactive"
),
""
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@CNENFRNL thanks for shorter version. however, In this scenerio I could not find a way to change formula for 3 months comparison. I have amended 3-4-5 as month to ie. 01/03/2022 for each 3 and same applied for other months. then connected it with date slicer and used datesinperiod formula but then failed.
@selimovd will check and reply. actually, I need only result as bbbb for 4th month. however I can add if clause on that formula to eliminate if previous month it was inactive keep and if it is active then remove.
Hey @selpaqm ,
sure, check the solution if's doing the job.
You could also add "bbbb" as a filter if you want to limit it to only one customer.
If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards
Denis
Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
Hey @selpaqm ,
you can check if the status changed compared to last month and only return a result if that was the case.
The following measure should do the work:
Change to Last Month =
VAR vTableWithStatusLastMonth =
ADDCOLUMNS (
Customer,
"@StatusLastMonth",
VAR vCustomerRow = Customer[customer]
VAR vMonthRow = Customer[Month]
RETURN
CALCULATE (
MAX ( Customer[status] ),
Customer[customer] = vCustomerRow,
Customer[Month] = vMonthRow - 1,
ALL ( Customer )
)
)
VAR vStatusLastMonthIfChanged =
MAXX (
vTableWithStatusLastMonth,
IF ( [@StatusLastMonth] <> Customer[status], [@StatusLastMonth], BLANK () )
)
RETURN
vStatusLastMonthIfChanged
The result looks then like this:
Please find attached my example report.
If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards
Denis
Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
solved the issue with replacing measure and use it as column formula and remove some parts and made it with 2 columns. also it can be merge but i want to be sure that formulas are working.
lastmonth =
VAR vCustomerRow = Customer[customer]
VAR vMonthRow = Customer[Month]
RETURN
CALCULATE (
MAX ( Customer[status] ),
Customer[customer] = vCustomerRow,
Customer[Month] = vMonthRow - 1,
ALL ( Customer )
)
countnew -customer = IF(Customer[lastmonth]=BLANK()&&Customer[status]="Active",1,IF(Customer[lastmonth]<>Customer[status]&&Customer[status]="Active",1,IF(Customer[lastmonth]=Customer[status]&&Customer[status]="Active", BLANK(), 0)))