Forum Discussion

Sachy123's avatar
Sachy123
Helper V
5 years ago
Solved

Calculated Column - flag

So my Data is in the following format.

 

 

ReportDateUpdateDateColumn AColumn B
31st December 202020/12/2020 8:00:001080
31st December 202015/12/2020 10:30:008800
31st December 202001/12/2020 8:00:004432
30th November 202029/11/2020 10:30:009099
30th November 202026/11/2020 14:00:334467
30th November 202015/11/2020 15:00:445020

 

I need to add one CALCULATED COLUMN, say FLAG, which becomes 1 when it encounters latest update date at that reporting period

ReportDateUpdateDateColumn AColumn BFlag
31st December 202020/12/2020 8:00:0010801
31st December 202015/12/2020 10:30:0088000
31st December 202001/12/2020 8:00:0044320
30th November 202029/11/2020 10:30:0090991
30th November 202026/11/2020 14:00:3344670
30th November 202015/11/2020 15:00:4450200

 

Need Help
Show and Tell
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    You can convert that expression to a measure and use it in a visual with the UpdateDate and ReportDate columns as follows

     

    NewFlagColumn =
    VAR vMaxThisPeriod =
        CALCULATE (
            MAX ( Report[UpdateDate] ),
            ALLEXCEPT (
                Report,
                Report[ReportDate]
            )
        )
    RETURN
        IF (
            MIN(Report[UpdateDate]) = vMaxThisPeriod,
            1,
            0
        )

     

    Regards,

    Pat

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    Here is a column expression that should work.  Replace Report with your actual table name.  Note that I had to convert your first two columns to Date and Date/Time.

     

    NewFlagColumn =
    VAR vMaxThisPeriod =
        CALCULATE (
            MAX ( Report[UpdateDate] ),
            ALLEXCEPT (
                Report,
                Report[ReportDate]
            )
        )
    RETURN
        IF (
            Report[UpdateDate] = vMaxThisPeriod,
            1,
            0
        )

     

    Regards,

    Pat

    • Sachy123's avatar
      Sachy123
      Helper V
      5 years ago

      strangely, it gives me a red line under the Calculate function and the tool tip is "CALCULATE is not a function" 

      BTW, the table is a result of DIRECT QUERY

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    You can convert that expression to a measure and use it in a visual with the UpdateDate and ReportDate columns as follows

     

    NewFlagColumn =
    VAR vMaxThisPeriod =
        CALCULATE (
            MAX ( Report[UpdateDate] ),
            ALLEXCEPT (
                Report,
                Report[ReportDate]
            )
        )
    RETURN
        IF (
            MIN(Report[UpdateDate]) = vMaxThisPeriod,
            1,
            0
        )

     

    Regards,

    Pat