Forum Discussion

Su2020's avatar
Su2020
New Member
2 years ago

Date functions in Power Bi

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")

Need Help

4 Replies

  • ChiragGarg2512's avatar
    ChiragGarg2512
    Icon for Solution Sage rankSolution Sage
    2 years ago

    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's avatar
      Su2020
      New Member
      2 years ago

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

      • ChiragGarg2512's avatar
        ChiragGarg2512
        Icon for Solution Sage rankSolution Sage
        2 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.