Forum Discussion

e2hass's avatar
e2hass
Frequent Visitor
10 years ago
Solved

is there a date difference in weeks/months visual?

Hi,

 

Is there a visual which shows the difference between two dates in weeks/months etc?

 

What i want to do is have like a card view and show delay in a project completion date. Cant seem to work out how to display such data. my table data looks something like below:

 

 

Project NoPlanned CompletionForecasted CompletionVariance in weeks
1a01/07/201607/07/20161
7c10/06/201624/06/20162
Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    10 years ago

    e2hass

     

    Not sure if the columns of “Variance in weeks” and “Variance in months” exist in your original dataset. If no, we can create two columns with following formulas.

    Variance in weeks = DATEDIFF ( Table1[Planned Completion], Table1[Forecasted Completion], WEEK )
    Variance in months = DATEDIFF ( Table1[Planned Completion], Table1[Forecasted Completion], MONTH )

     

    Then drag Slicer (Project No for Field) and Card chart into your canvas. The card will show the delay when you select a project in the slicer.

     

    Best Regards,

    Herbert

3 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    e2hass

     

    Not sure if the columns of “Variance in weeks” and “Variance in months” exist in your original dataset. If no, we can create two columns with following formulas.

    Variance in weeks = DATEDIFF ( Table1[Planned Completion], Table1[Forecasted Completion], WEEK )
    Variance in months = DATEDIFF ( Table1[Planned Completion], Table1[Forecasted Completion], MONTH )

     

    Then drag Slicer (Project No for Field) and Card chart into your canvas. The card will show the delay when you select a project in the slicer.

     

    Best Regards,

    Herbert

    • e2hass's avatar
      e2hass
      Frequent Visitor
      10 years ago

      v-haibl-msft

       

      Thanks for your reply. The calculated columns do not work for negative weeks. Sometimes Forecasted could be before Planned. DATEDIFF throws error saying start date must be greater then date. Do you know what function i can use to solve this? or shall i just leave this at the datasource... 

       

      overall perfect solution. 

      • v-haibl-msft's avatar
        v-haibl-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        10 years ago

        e2hass

         

        We can use IF function to solve it.

         

        Variance in weeks = 
IF (
    Table1[Forecasted Completion] >= Table1[Planned Completion],
    DATEDIFF ( Table1[Planned Completion], Table1[Forecasted Completion], WEEK ),
    - DATEDIFF ( Table1[Forecasted Completion], Table1[Planned Completion], WEEK )
)

         

        Variance in months = 
IF (
    Table1[Forecasted Completion] >= Table1[Planned Completion],
    DATEDIFF ( Table1[Planned Completion], Table1[Forecasted Completion], MONTH ),
    - DATEDIFF ( Table1[Forecasted Completion], Table1[Planned Completion], MONTH )
)

         

        Best Regards,

        Herbert