Date functions in Power Bi

[Su2020]

Hi all,

I'm trying to make the exact calculations below in Power BI, and while they work flawlessly in Excel, I'm having trouble getting them to function in Power Bi. Could someone please assist me?

1- Status Update Days =IF(ISBLANK(POSTED ON),"0",TODAY()-POSTED ON)
2- Status Update Comment =IF(ISBLANK(Status Update Days),"Project has never been updated",IF(TODAY()*Status Update Days<=0,"Project has not been updated",IF(Status Update Days<=14,"Updated",IF(Status Update Days>=30,"Project is not updated more than 1 month","Project is not updated more than 2 weeks"))))
3- Project total Past Due Date Days =IF(ISBLANK(start data),"",TODAY()-start date)
4- Project Due Date Status =IF(ISBLANK(Due date),"Project has no due date",IF(TODAY()-due date>1,"Project has past due date","Project is not due yet"))
5- Projects Deliverables Status =IF(OR((Projects Deliverables)="Not Provided", ISBLANK(Projects Deliverables)), "Not Provided","Provided")

[Anonymous]

Hi,

Thanks for the solution @ChiragGarg2512  provided, it is excellent, and I want to offer some more informatio for user to refer to.

hello @Su2020 , based on your description, you can modify your code to the following in power bi, you need to create calculated columns in table.

Status Update Days = IF(ISBLANK([POSTED ON]),0,DATEDIFF([POSTED ON],TODAY(),DAY))

Status Update Comment =
IF (
    ISBLANK ( [Status Update Days] ),
    "Project has never been updated",
    IF (
        DAY ( TODAY () ) * [Status Update Days] <= 0,
        "Project has not been updated",
        IF (
            [Status Update Days] <= 14,
            "Updated",
            IF (
                [Status Update Days] >= 30,
                "Project is not updated more than 1 month",
                "Project is not updated more than 2 weeks"
            )
        )
    )
)

Project total Past Due Date Days =IF(ISBLANK([start data]),0,DATEDIFF([start date],TODAY(),DAY))

Project Due Date Status =
VAR a =
    DATEDIFF ( [due date], TODAY (), DAY )
RETURN
    IF (
        ISBLANK ( [Due date] ),
        "Project has no due date",
        IF ( a > 1, "Project has past due date", "Project is not due yet" )
    )

Projects Deliverables Status =
IF (
    OR (
        [Projects Deliverables] = "Not Provided",
        ISBLANK ( [Projects Deliverables] )
    ),
    "Not Provided",
    "Provided"
)

Sample Output

As @ChiragGarg2512  mentionded, if you want to extract the day of today, you need to use the day() funciton, and you can use DATEDIFF() function to calculate the difference between two dates.

You can refer to the following link abount the functions.

DATEDIFF function (DAX) - DAX | Microsoft Learn

DAY function (DAX) - DAX | Microsoft Learn

If the solutions @ChiragGarg2512  and I provided help you solve the problem, please consider to mark them as the solution.

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.

[ChiragGarg2512]

1- POSTED ON should be a number if it has to be subtracted from TODAY()
2- Today() returns a date, not the day.

3-  Today()-startdate will return a date instead of the number of days in between

[Su2020]

so, what should I use instead of today()?

[ChiragGarg2512]

in case of 

2- use day(today()) to get the day date. for ex, day(17/04/2024) gives 17.

3- use int(Today()-startdate), this should give the day difference between two dates 

Today() provides the current date, it is up to the user to extract the date, day, month, quarter, and year from it.