Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Measure in Calculated Column

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

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

3 Replies

  • 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's avatar
      Anonymous
      Not applicable

      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's avatar
        Floriankx
        Solution Sage

        Hello,

         

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