Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
selpaqm
Helper V
Helper V

if clause change with month slicer

Hi,

 

I have a table as below. 

customerstatusMonth
aaaaactive3
bbbbinactive3
ccccactive3
bbbbactive4
ccccinactive4
aaaainactive4
aaaaactive5
bbbbactive5
eeeeactive5

 

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.

1 ACCEPTED 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)))

View solution in original post

6 REPLIES 6
CNENFRNL
Community Champion
Community Champion

 

_ = 
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"
            ),
        ""
    )

 

Record_2022_06_28_15_00_14_250.gif


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.

selpaqm
Helper V
Helper V

@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

selimovd
Super User
Super User

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:

selimovd_0-1656419124080.png

 

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)))

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!