cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
slapdragon Frequent Visitor
Frequent Visitor

Current date to week difference

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

3 REPLIES 3
Community Support Team
Community Support Team

Re: Current date to week difference

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

81.png

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 Frequent Visitor
Frequent Visitor

Re: Current date to week difference

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

slapdragon Frequent Visitor
Frequent Visitor

Re: Current date to week difference

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?

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 52 members 958 guests
Please welcome our newest community members: