Up to date "Current date" based slicer

[Arkhos94]

I have a database with a list of special offers and when they end (in Date format, not in Datetime)

My goal is to have a slicer to know if the special offer is over (end date < today) or not (end date > today)

I found the following way of doing it :

- make a copy of my "end date" column, change the copy format in datetime then add a new column "Open ?" with the following formula :

if [#"End Date"]< DateTime.LocalNow() then "Closed" else "Opened"

It seems to work and put Opened or Closed depending on today's date.

Question : once my power BI is published, will my "Open ?" column continue to update or will it stay as it was when I click on publish ?

If the "Open ?" column doesn't update, is there any way to create a slicer to do what I want ?

[parry2k · Accepted solution]

@Arkhos94 yes you are right, whenever refresh happens. You can set the schedule in the Power BI service to refresh the dataset, with Power BI pro you can have 8 refreshes a day, with 30 mins apart. 

[Arkhos94]

thank you @parry2k

I refresh my data & republish every 15 days, so if I understand correctly my "Open ?" column will update every 15 days ?

Is there any way to have it updated "live" ?

[parry2k]

@Arkhos94 it will update every time data refresh happens.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡