Measure in Calculated Column

[Anonymous]

Hi everyone, I', having a DAX problem. I've tried to solve it but to no avail. I'm trying to create a calculated column to flag if a record should show as 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 please?

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")

 I need to add a few more conditions also, such as if this other column says x then it's this year and so on but I've fallen at the first hurdle

[Floriankx · Accepted solution]

Hello you could dim your measure inside your formula:

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

[Anonymous]

Thanks. Funnily enough it occurred to me to try this after posting. I'll have a go now and mark as accepted if it works.

General question on the same topic: are measures allowed in calculcated columns?

[Floriankx]

Hello,

generally they are, but calculated columns are row based and measure column based so I try to avoid it if possible.