Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

You need help writing DAX.

I want to show "New Active Customers" according to the following screenshot. Need help with writing measures for two visual objects. First. In which "New Active Customers" shows from the Minimu...
  • PaulDBrown's avatar
    5 years ago

    Anonymous 

    See if this works for you (apologies since I din't apply the colour coding you have in your original):

     

    1) To calculate the new clients by month:

    New clients in month =
    VAR PrevYM =
         ( MAX ( FactDigitalStrategy[Year] ) - 1 ) * 100
            + RIGHT ( SELECTEDVALUE ( FactDigitalStrategy[MonthYearNo] ), 2 )
    VAR ActualClients =
        VALUES ( FactDigitalStrategy[ClientKey] )
    VAR PrevYearMonthClients =
        CALCULATETABLE (
            VALUES ( FactDigitalStrategy[ClientKey] ),
            FILTER (
                ALL ( FactDigitalStrategy ),
                FactDigitalStrategy[MonthYearNo] = PrevYM
            )
        )
    RETURN
        COUNTROWS ( EXCEPT ( ActualClients, PrevYearMonthClients ) )

    2) To filter the visuals based on the selection in the slicers, I created a new disconnected table for the new and active clients (and used this table for the visuals)

    3) To filter the visuals, I created a measure to use in the filters for the visuals in the filter pane:

    New client table (Filtered) =
    IF (
        SELECTEDVALUE ( '1 New client table'[MonthYearNbr] )
            <= SELECTEDVALUE ( DimDate[MonthYearNbr] ),
        1
    )

      

    Let us know if you need further help

    I'm attachiing the sample PBIX file