how to use comma separated value as a filter in calculate function in dax

[sandip]

Hi,

I am using below dax expression:

totalGrossPurchase =

var vDate = DATE(YEAR(SELECTEDVALUE(CAL4[Current_End])), Month(SELECTEDVALUE(CAL4[Current_End])), 01)

var v11MonthsBeforDt = DATE(YEAR(vDate),MONTH(vDate)-11,DAY(vDate))

var v11MonthsBeforMonthName = TRIM(FORMAT(v11MonthsBeforDt, "YYYY-MMM"))

var v10MonthsBeforDt = DATE(YEAR(vDate),MONTH(vDate)-10,DAY(vDate))

var v10MonthsBeforMonthName = TRIM(format(v10MonthsBeforDt, "YYYY-MMM"))

var v9MonthsBeforDt = DATE(YEAR(vDate),MONTH(vDate)-9,DAY(vDate))

var v9MonthsBeforMonthName = TRIM(format(v9MonthsBeforDt, "YYYY-MMM"))

/*var vFinalYearMonthTxt = v11MonthsBeforMonthName&","&v10MonthsBeforMonthName&","&v9MonthsBeforMonthName*/

return CALCULATE(SUM('SAVO_QBR_TEMP'[TOTAL GROSS SALES]), PATHCONTAINS("2018-Mar,2018-Feb", Dates[YearMonthText]))

But it does not work for me. Please tell me where I am worng

[Anonymous · Accepted solution]

@sandip , try this DAX code:

totalGrossPurchase =
VAR vDate =
    DATE ( YEAR (
        SELECTEDVALUE ( CAL4[Current_End] )
    ), MONTH (
        SELECTEDVALUE ( CAL4[Current_End] )
    ), 01 )
VAR v11MonthsBeforDt =
    DATE ( YEAR ( vDate ), MONTH ( vDate ) - 11, DAY ( vDate ) )
VAR v11MonthsBeforMonthName =
    TRIM (
        FORMAT (
            v11MonthsBeforDt,
            "YYYY-MMM"
        )
    )
VAR v10MonthsBeforDt =
    DATE ( YEAR ( vDate ), MONTH ( vDate ) - 10, DAY ( vDate ) )
VAR v10MonthsBeforMonthName =
    TRIM (
        FORMAT (
            v10MonthsBeforDt,
            "YYYY-MMM"
        )
    )
VAR v9MonthsBeforDt =
    DATE ( YEAR ( vDate ), MONTH ( vDate ) - 9, DAY ( vDate ) )
VAR v9MonthsBeforMonthName =
    TRIM (
        FORMAT (
            v9MonthsBeforDt,
            "YYYY-MMM"
        )
    )
VAR Result =
    CALCULATE (
        SUM ( 'SAVO_QBR_TEMP'[TOTAL GROSS SALES] ),
        Dates[YearMonthText]
            IN {
            v11MonthsBeforMonthName,
            v10MonthsBeforMonthName,
            v9MonthsBeforMonthName
        }
    )
RETURN
    Result

I kept everything the same, and then added a new variable Result that does what you're looking for.

You could probably build a similar filter only using the date column to get only the dates between 9 and 11 months before the selected period, that may be a little faster.