Forum Discussion

kanth123's avatar
kanth123
Helper I
4 years ago
Solved

Flag Column based on date compare.

Hi, 

 

Trying to create a custom flag column based on below logic but 

Calculate Measures:

1.Calendardate - Max(Calendar[Date])

2. Calendardate PY - Date(Year(Calendar[Date])-1,Month(Calendar[Date]),Day(Calendar[Date]))

 

Flag_Column:

If ([Release Date]  <= [Calendardate PY] &&  if (([Release Date]) >= EDATE(DATE(YEAR(Calendardate PY),MONTH(Calendardate PY),1),-11),"Include","Exclude"))

 

Above logic works for current month and Current year and Its not working when"Jan" month is selected. I'm expecting Max[Calendar[Date]) to 1/31/2022 but it still considering 2/17/2022.

 

Any suggestion..

 

Thanks in Adavance. 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi kanth123 ,

    As amitchandak said, the value of calculated column can't be changed by slicer, they are calculated at data refresh. For more about calculated column, refer to this article: Difference between calculated columns and measures? 

     

    Your expected result can also be avhieved in a measure. In a measure, you should use MAX/MIN before a column to return the value in the current row. This is the measure formula.

    Flag =
IF (
    MAX ( [Release Date] ) <= [Calendardate PY]
        && MAX ( [Release Date] )
            >= EDATE (
                DATE ( YEAR ( [Calendardate PY] ), MONTH ( [Calendardate PY] ), 1 ),
                -11
            ),
    "Include",
    "Exclude"
)

     The value of Flag will change by the selected value in the slicer.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies