Calculated Column

[gauravnarchal]

Hello - I want to add two columns in my calendar table to mark

1. Yesterday was the "previous day" and the current date is "today".
2. The current date is "today" and all previous dates are "previous dates" and all future dates are "future dates".

Thanks

[AlexisOlson · Accepted solution]

A simple switching column should do the trick but it will require you to refresh your model every day since today changes.

DateCategory =
SWITCH (
    TRUE (),
    DimDate[Date] = TODAY (), "today",
    DimDate[Date] = TODAY () - 1, "previous day",
    DimDate[Date] < TODAY (), "previous dates",
    DimDate[Date] > TODAY (), "future dates"
)

[v-zhangti · Accepted solution]

Hi, @gauravnarchal 

1. Create a new calendar table, assuming that these are the current dates.

Date = 
CALENDAR ( DATE ( 2021, 10, 01 ), DATE ( 2021, 10, 15 ) )

   2. Create two new columns to represent previous dates and future dates respectively.

Previous day = 
DATEADD ( 'Date'[Today], -1, DAY )

Future dates = 
DATEADD ( 'Date'[Today], +1, DAY )

If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

Best Regards,

Community Support Team _Charlotte

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

[AlexisOlson · Accepted solution]

A simple switching column should do the trick but it will require you to refresh your model every day since today changes.

DateCategory =
SWITCH (
    TRUE (),
    DimDate[Date] = TODAY (), "today",
    DimDate[Date] = TODAY () - 1, "previous day",
    DimDate[Date] < TODAY (), "previous dates",
    DimDate[Date] > TODAY (), "future dates"
)