Trying to use FILTER() to filter based on a comparison of two strings

[Roseventura]

Hi,

I have a Date table that contains formatted dates, but also contains text strings as in "2017-11" and "2017-Q4", etc.  This is because we use a 4-4-5 fiscal calendar.  So if the date is 11/27/17, the fiscal month is 12 and the Year-Mo is 2017-12.

My fact (Invoices) table also has these text fields which are associated with the Invoice Date.  What I'm trying to do is this, but it's not working:

        MTD Sales = calculate(sum(Invoices[Ext Sales Amount]),filter(Invoices,Invoices[Year-Mo]=DATES[Year-Mo]))

The reason it's not working is because I'm trying to compare two strings as opposed to 2 values.  The measure will accept a string for the left side of the equal sign, Invoices[Year-Mo], but not for the right side of the equal sign DATES[Year-Mo].  How can I get this to work?  I'm working with a fact table that has multiple years so that's why I need to use the "YYYY-MM" string to compare.

Rose

[DAX0110]

Hi @Roseventura, you could try this version:

MTD Sales =

    VAR currentDate = MAX( DATES[Date] )

    VAR currentMonth = MAX( DATES[Year-Mo] )

    RETURN

        calculate(

           sum( Invoices[Ext Sales Amount] )

          , ALL(DATES)

          , DATES[Year-Mo] = currentMonth

          , DATES[Date] <= currentDate

       )

[Roseventura · Accepted solution]

DAX0110,

Your measure worked with a slight alteration.  The only thing I had to change was that my variables had to look at the Invoice Date and Invoice Year-Mo in order to compare it to my DATES table (which is actually called PBI_FSCAPF....don't ask me I didn't name it!):

Here's my finished measure:

TY Month Sales =
    VAR CurrDate = MAX( PBI_Invoice_Multi[Formatted Inv Date] )
    VAR CurrMonth = MAX( PBI_Invoice_Multi[Year-Mo] )
    RETURN
        calculate(sum([Ext Sales Amount]),All(PBI_FSCAPF),PBI_FSCAPF[YEAR-MO] = CurrMonth,PBI_FSCAPF[LUDATE]<=CurrDate-1)

Thanks for your help!

Rose