cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sandip Frequent Visitor
Frequent Visitor

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
ChrisHaas Established Member
Established Member

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.

View solution in original post

1 REPLY 1
ChrisHaas Established Member
Established Member

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.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 240 members 2,418 guests
Please welcome our newest community members: