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

v-haibl-msft's avatar
v-haibl-msft
Microsoft 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
    Microsoft 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