Forum Discussion
You need help writing DAX.
- 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
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