is there a date difference in weeks/months visual?

[e2hass]

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 No	Planned Completion	Forecasted Completion	Variance in weeks
1a	01/07/2016	07/07/2016	1
7c	10/06/2016	24/06/2016	2

[v-haibl-msft · Accepted solution]

@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]

@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]

@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