return text based off calender date

[Anonymous]

Hello.

I am new to Power Bi and need some help.  I have a data pull from excel that shows a due date for each line item.  Based off that due date and looking at a calendar, I am able to identify the status of that item.  For example:  Assume today's date is 10-25-23.  Anything prior to today is considered Past Due.  Anything with a due date of today is considered Due Today.  Anything due in the next two days (making sure to account for weekends and holidays) is Due in Two Days.  Anything further out than that is On track.

My goal is to eventually have the data auto pull from the website and update the dashboard automatically.  Is there a way to create a column in Power BI to auto update the status based off the due date and keeping weekends / holidays in mind?  I came up with a nested formual in excel, but think I am making this much harder than it needs to be.  Also, my calculation would have to be updated constantly to account for weekends / holidays.  At that point it was easier to just manually make the updates as I was concened about it returning the correct response.

Can someone please help?

[EylesIT]

@Anonymous, further to my first reply, I have created a solution which takes working days into account.

Create a Date dimension table which has a WorkingDays column as an integer. For working days set this to 1, for non-working days set it to 0. In my example I used this data for October:

Date   WorkingDays

01-Oct-23	0
02-Oct-23	1
03-Oct-23	1
04-Oct-23	1
05-Oct-23	1
06-Oct-23	1
07-Oct-23	0
08-Oct-23	0
09-Oct-23	1
10-Oct-23	1
11-Oct-23	1
12-Oct-23	1
13-Oct-23	1
14-Oct-23	0
15-Oct-23	0
16-Oct-23	1
17-Oct-23	1
18-Oct-23	1
19-Oct-23	1
20-Oct-23	1
21-Oct-23	0
22-Oct-23	0
23-Oct-23	1
24-Oct-23	1
25-Oct-23	1
26-Oct-23	1
27-Oct-23	1
28-Oct-23	0
29-Oct-23	0
30-Oct-23	1
31-Oct-23	1

Then in your data table, create a Calculated Column called WorkingDaysTillDueDate with this DAX expression:

WorkingDaysTillDueDate = 
    VAR duedate = YourTable[DueDate]
    VAR today = TODAY()
    RETURN
        SWITCH(TRUE(),
            duedate = today, 0,
            duedate < today,
            CALCULATE(
                0 - SUM(dimDate[WorkingDays]),
                dimDate[Date] >= duedate,
                dimDate[Date] < today
            ),
            duedate > today,
            CALCULATE(
                SUM(dimDate[WorkingDays]),
                dimDate[Date] > today,
                dimDate[Date] <= duedate
            )
        )

Now create another Calculated Column called DueStatus with this DAX expression:

DueStatus = 
    VAR days = YourTable[WorkingDaysTillDueDate]
    RETURN
        SWITCH(TRUE(),
            days < 0, "Past Due",
            days = 0, "Due Today",
            days <= 2, "Due in Two Days",
            "On Track"
        )

When I run this today (25 Oct 2023) it gives me the following results for dates from 23 Oct to 30 Oct. And whenever you refresh the report, it will recalculate both Calculated Columns based on the current today's date.

You will need to maintain the dimDate table by populating it with any future dates and what the WorkingDays field should be for each.

Hopefully this helps.

[Anonymous]

Thank you very much for your help!!  I have been able to create the calculation in excel to produce the correct Date Status.  I have a report in Power Bi that I have recently created that is linked to a SP site and updates automatically.  My next goal is to figure out how to add this new calculation / rows to the current report so that I can have the details linked.  I am viewing videos online to learn how to do this (very new to Power BI).  Fingers crossed I can update this soon.  🙂  Again, your help is very much appreciated.

[EylesIT]

@Anonymous, this is a fairly standard problem. The way to solve it would be to create a Date dimension in your Power BI report. The Date dimension can have a column (e.g. IsWorkingDay) that indicates is a day is a working day or not. How you populate that column will depend on the needs of your solution. Having a Date dimension in place will make it easier to do many other time-based calculations.

Once you have created a Date dimension, you can then add a Calculated Column to your source data to work out what the DueStatus for each row is. The logic in this calculation will use today's date, the DueDate, and the Date dimension's IsWorkingDay flag to produce the Past Due, Due Today, Due in Two Days, On Track, or whichever other statuses are needed.

A basic demonstration of the Calculated Column, without using a Date dimension, is shown below.

DueStatus = 
    VAR daysdiff = DATEDIFF(TODAY(), YourTable[DueDate], DAY)
    RETURN
    SWITCH(TRUE(),
        daysdiff < 0, "Past Due",
        daysdiff = 0, "Due Today",
        daysdiff <= 2, "Due in Two Days",
        "On Track"
    )

I am using  this test data for YourTable:

DueDate

23 Oct 2023

24 Oct 2023

25 Oct 2023

26 Oct 2023

27 Oct 2023

28 Oct 2023

29 Oct 2023

30 Oct 2023

And this is the output:

Is this the sort of thing you are looking for?