Getting status of day from date of data ( Yesterday, Today, Tomorrow )

[Anonymous]

Hi Team

im back again with another question.

i want to get column result same as "REMARK" , i will keep date beside those date as blank ( "" )

i already used DAX below, but result not same as REMARK
Status_day = if(Table[DATE] = today(),"today", if (Table[DATE] = today()-1,"yesterday", if(Table[DATE] = today() + 1, "tomorrow")))

thanks

syaiful

[Anonymous]

Hi @Anonymous ,

You can create a calculated column in the table using below formula. As in your case, time is also available in the value hence we need to get date from timestamp field and need to compare.

Status = IF(FORMAT('Table (2)'[Column1], "dd/MM/YYYY")=FORMAT(TODAY(), "DD/MM/YYYY"), "Today", IF(FORMAT('Table (2)'[Column1], "dd/MM/YYYY")=FORMAT(TODAY()-1, "DD/MM/YYYY"),"Yesterday", IF(FORMAT('Table (2)'[Column1], "dd/MM/YYYY")=FORMAT(TODAY()+1, "DD/MM/YYYY"), "Tomorrow", "")))

This will show the status you given in the sample.

If you are good with the solution, please mark this as answer.

Thanks,

Sunil

[Anonymous]

i did .

but result only blank . i see there are data for today transaction but same result as blank .

any suggestion ??

my date already type as date and time format

[V-pazhen-msft]

@Anonymous 

This is because your date column includes time frame. With your formula I have resulted the same blank for NOW() function, but works for Today() or created date column. So I think you would need to recreated your date column.

Paul
Best

[Anonymous]

hi paul

i already change type of date time to date only , but why result still blank also ??

[V-pazhen-msft · Accepted solution]

@Anonymous 

Well, I guess change the display format will not change essence of the value. You need to use Format function like @Anonymous did. 

I recommend you create a new date column to format your date and remove the timestamps:

FORMAT('Table'[Date],"YYYY/MM/DD")

And then stick with your IF formula. 

Paul,
Best.

Paul

[Anonymous]

thanks @V-pazhen-msft 

created new column , used that column as trigger of day status

[Anonymous]

I changed the date format to dd/MM/yyyy the applied the below formula. It worked for me...:)

Status = IF(FORMAT('Table (3)'[Date],"DD/MM/YYYY")=FORMAT(TODAY(), "DD/MM/YYYY"), "Today", IF(FORMAT('Table (3)'[Date],"DD/MM/YYYY")=FORMAT(TODAY()-1, "DD/MM/YYYY"),"Yesterday", IF(FORMAT('Table (3)'[Date],"DD/MM/YYYY")=FORMAT(TODAY()+1, "DD/MM/YYYY"), "Tomorrow", "")))

[Anonymous]

can u show me ,,

u can attach new pbi here

thanks

[V-pazhen-msft]

@Anonymous 

My testfile:
https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/EdQSAwyp021Kl0ucyNWUENYBv8e404l6HszYgpa-lRvvOA?e=LKMsrd 

Best,
Paul

[Mariusz]

Hi @Anonymous 

Relative Date Dimension can help you achieve this requirement.

 https://community.powerbi.com/t5/Community-Blog/Relative-Date-Dimension/ba-p/779039

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Anonymous]

relative filter gv me option while showing ,

my goal is getting name column ( yesterday | today | tomorrow ) , which only able to do with nested if from new column

[Anonymous]

Could you provide some sample dates. In my case i can see the desired result.

[Anonymous]

hi @Anonymous 

this is my date format