cancel
Showing results for
Did you mean:
Helper I

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Solution Sage

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

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

Solution Sage

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

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

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors