Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Different Approach to Date Calculation

Hello,

I currently have a report that essentially takes two values [Year Day No] and [Today Year Number]

[Year Day No] is the year and day number merged of the ending date of a production order

[Today Year Number] is the year and day number of today's date merged.

My current calculation can be seen below which is wrong.

Essentially I was using this to get my "Daily State" outcome, the problem is of course when the end date of a production order is in 2025 and in the beginning of the year the [year day number] will be smaller than the [today year number] thus resulting in a "Late" when it really is not. Example below.

I know this is probably very confusing, but I need to find a way to re-calculate this so that my "Daily State" is correct.

Any help would be much appreciated!

Thanks

Jeremy

2 ACCEPTED SOLUTIONS
Super User

don't use an awkward [Year Day No]  - use an awkward "Days since 12/30/1899" number which you get when you convert your date column to "Whole Number" . That also works across year boundaries.

Community Support

Hi,

Thanks for the solution @lbendlin  provided, and i want to offer some more information for user to refer to.

hello @Grimfandango227 , based on your desscrition, you don't need to conver the date the whole number, you can change your code to the following.

`````` =Table.AddColumn(#"Changed Type1", "Daily State", each let _today=DateTime.Date(DateTime.LocalNow()),
_7today=Date.AddDays(_today,-7),
_13today=Date.AddDays(_today,+13),
_format=Number.ToText(Date.Month([Ending_date]))&Number.ToText(Date.Day([Ending_date]))
in   if [Ending_date]<_7today then "Late" else if [Ending_date]>_13today then "Future" else if [Ending_date]=_today then "Today" else _format)``````

Output

And you can refer to the attachments.

Best Regards!

Yolo Zhu

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

2 REPLIES 2
Community Support

Hi,

Thanks for the solution @lbendlin  provided, and i want to offer some more information for user to refer to.

hello @Grimfandango227 , based on your desscrition, you don't need to conver the date the whole number, you can change your code to the following.

`````` =Table.AddColumn(#"Changed Type1", "Daily State", each let _today=DateTime.Date(DateTime.LocalNow()),
_7today=Date.AddDays(_today,-7),
_13today=Date.AddDays(_today,+13),
_format=Number.ToText(Date.Month([Ending_date]))&Number.ToText(Date.Day([Ending_date]))
in   if [Ending_date]<_7today then "Late" else if [Ending_date]>_13today then "Future" else if [Ending_date]=_today then "Today" else _format)``````

Output

And you can refer to the attachments.

Best Regards!

Yolo Zhu

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

Super User

don't use an awkward [Year Day No]  - use an awkward "Days since 12/30/1899" number which you get when you convert your date column to "Whole Number" . That also works across year boundaries.

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors