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

Data formatting with date

I am trying to create a graphic that shows data for projects where the projects and within time, almost due or overdue and then display this in a graph, pie chart etc but im struggling with the formatting. has anyoine else tried this?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
erik_tarnvik Established Member
Established Member

Re: Data formatting with date

OK assuming you have a table Proj like this:

ProjectDueActual
A8/1/20177/30/2017
B8/1/20178/10/2017
C8/1/20178/5/2017
D8/1/20178/10/2017

 

 

Add two calculated columns to Proj:

Diff = IF(Proj[Due] > Proj[Actual],
          -DATEDIFF(Proj[Actual], Proj[Due], DAY),
          DATEDIFF(Proj[Due], Proj[Actual], DAY))

Status = IF(Proj[Diff] > 6,
            "Overdue",
            IF(Proj[Diff] > 0,
               "Delayed",
               "On Time"))

You can now easily display a simple table visual:

image.png

by placing the columns of Proj like this and chnaging the data colors appropriately:

image.png

View solution in original post

3 REPLIES 3
erik_tarnvik Established Member
Established Member

Re: Data formatting with date

Can you provide some more specifics on your data tables and what you want the graph to look like?
Neiltc Frequent Visitor
Frequent Visitor

Re: Data formatting with date

Hi Erik,

I have an excel sheet with about 100 rows of current projects, each has a due date.  In excel i would use conditional formatting to show green if the projects were within delivery time, yellow if they were within 6 days of the due date and red if they were over the due date. I am looking for a way to graphically represent this, even a bar chart of the three categories but I am unsure of the query that is needed...

Highlighted
erik_tarnvik Established Member
Established Member

Re: Data formatting with date

OK assuming you have a table Proj like this:

ProjectDueActual
A8/1/20177/30/2017
B8/1/20178/10/2017
C8/1/20178/5/2017
D8/1/20178/10/2017

 

 

Add two calculated columns to Proj:

Diff = IF(Proj[Due] > Proj[Actual],
          -DATEDIFF(Proj[Actual], Proj[Due], DAY),
          DATEDIFF(Proj[Due], Proj[Actual], DAY))

Status = IF(Proj[Diff] > 6,
            "Overdue",
            IF(Proj[Diff] > 0,
               "Delayed",
               "On Time"))

You can now easily display a simple table visual:

image.png

by placing the columns of Proj like this and chnaging the data colors appropriately:

image.png

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors