Calculated Column compare to measure

Question

yet another Power BI problem I'm having that I've tried to solve to no avail..

I'm trying to create a calculated column to flag if a record is in this year.

I have a planning table:

FiscalYear | ReportDate  |  Status   | 
2017       | 01/4/2018   | Open      | 
2018       |             | Open      | 
2017       |             | Closed    |
2016       | 01/10/2016  | Complete  | 
2016       | 01/8/2017   | Complete  |

I have a measure [Current Year] that returns 2018/19 as text.

I have a date table with every date, sample below of selected years:

FiscalYear | FiscalYearSlash|  Date     | 
2017       | 2017/18        | ..........| 
2018       | 2018/19        | ..........| 
2017       | 2017/18        | ..........| 
2016       | 2016/17        | ..........| 
2016       | 2016/17        | ..........|

I can return FiscalYearSlash in the planning table with:

CALCULATE(VALUES(Dates[FiscalYearSlash]),
                        FILTER(Dates,
                        Dates[FiscalYear]=Planning[FiscalYear]))

but when I put this as the condition in an IF statement, it comes out false every time. Both the column and the measure are text. Any ideas?

Wondering if it's something to do with context transition. I've watched some of the SQLBI.com videos and understand them but when it comes to applying it.. struggling a bit. Thanks.

Here's the IF statement I'm trying:

IF( CALCULATE(VALUES(Dates[FiscalYearSlash]),
                        FILTER(Dates,
                        Dates[FiscalYear]=Planning[FiscalYear]))
= CurrentYear,"yes","no")

deethreenovice deethreenovice · Accepted Answer · 2018-05-09T08:54:48

I've solved this. I returned the current year as a VAR in the function, as opposed to a measure:

VAR CurrentYear =  CALCULATE(VALUES(Dates[FiscalYearSlash]), 
                        FILTER(Dates,Dates[Date]=TODAY()))
RETURN
SWITCH(TRUE(),
            CALCULATE(VALUES(Dates[FiscalYearSlash]),
                        FILTER(Dates,
                        Dates[FiscalYear]=Planning[FiscalYear]))=CurrentYear,
            "Yes","No"
)

Calculated Column compare to measure

1 Answers