Current date to week difference

[slapdragon]

Not sure if the title just gives a proper idea of what my query is...

What I am working on is a report of the tasks my team is working on. We track the progress of each task by the date when they were last commented on. What I am trying to achieve is, when a task has a comment sometime this current week, I want the value as "Last one week". If the comment was sometime last week, it should say "Last Week", then "Two Weeks Ago" and anything older than that would be "No Progress". 

I have currently achieved this using an excel formulas since an Excel is file is the data source for this Power Bi project.

I also have a couple of conditions in this formula. I wasnt the value only if the status of the task is "In Progress", because we are not tracking tasks with any other status. Below is the formula I now use.

In the formula, L2 is the 'status' column, R2 is the 'last comment date; column. There is also another worksheet called Reference that contains four entries.. A1 = Today(), A2 = A1-7, A3=A2-7, A4 =A3-7.

=IF(L2="In Progress",IF(AND(R2<=Reference!$A$1,R2>=Reference!$A$2),"Last one week",IF(AND(R2<Reference!$A$2,R2>=Reference!$A$3),"Two weeks ago",IF(AND(R2<Reference!$A$3),"No Progress"))),"")

I was wondering if it was possible to get this done in Power Bi using DAX

[dax]

Hi slapdragon,

According to your description, it seems that you want to define result based on date, right? If so, you could try  to use below measure to see whether it works or not

Measure 9 =
IF (
    MIN ( task[type] ) = "a",
    IF (
        MIN ( task[date] ) <= TODAY ()
            && MIN ( task[date] )
                > TODAY () - 7,
        "last one week",
        IF (
            MIN ( task[date] )
                <= TODAY () - 7
                && MIN ( task[date] )
                    > TODAY () - 14,
            "two week ago",
            IF ( MIN ( task[date] ) <= TODAY () - 14, "no process", "" )
        )
    ),
    ""
)

Best Regards,

Zoe Zhi

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

[slapdragon]

Hi @dax ,

I tried the suggested Measure, but somehow, it doesn't seem to fetch any information. The creation of the measure was successful though.

Progress = 
IF (
    MIN ( Jira_Main_1[Status] ) = "In Progress",
    IF (
        MIN ( Jira_Main_1[LastComment] ) <= TODAY ()
            && MIN ( Jira_Main_1[LastComment] )
                > TODAY () - 7,
        "Last one week",
        IF (
            MIN ( Jira_Main_1[LastComment] )
                <= TODAY () - 7
                && MIN ( Jira_Main_1[LastComment] )
                    > TODAY () - 14,
            "Two Weeks ago",
            IF ( MIN ( Jira_Main_1[LastComment] ) <= TODAY () - 14, "No Progress", "" )
        )
    ),
    ""
)

I then use the newly created measure to create a stacked bar chart and all I get is "Can't display the visual". Can you help please?

[slapdragon]

I am very sorry, but I am very confused about why 'MIN' is used here. If you don't mind, would you be able to explain to me about what this does? I have tried replacing task[date] with the right values, but it didn't seem to work (I know it is my fault.)