DATEDIFF evolution of a date over the year

[Ety]

Hello,

I need to calculate the evolution of a due date over the year.

For example in Contoso, If I filter on the SalesKey=2201, I get the following result which is correct where YearMonthShort is a column of Calendar, DateKey is a column of Sales and EndOfMonth Calendar i a measure defined like that :

EndOfMonth Calendar = CALCULATE(MAX('Calendar'[DateKey]); FILTER(ALL('Calendar');'Calendar'[DateKey]<=MAX('Calendar'[DateKey])))

But now, I'd like to calculate the difference in days between those dates.

I tried a calculated column with a DATEDIFF function but it doesn't work. I tried measures but without any success.

Any help or explanation would be greatly appreciated.

Regards,

Ety.

[Chthonian]

Hi @Ety ,

A happy new year to you and your family too. Hope you had a great festive season.

Like you I thought I would get an email when replies were posted but it did not happen in this case, so I apologise for the delayed response. 

Following your reply I was having a play and I owe you an apology. The DATEDIFF function is a column function more so that something that can be used in a measure and the issue comes down to row context within the measure. 

To get around this limitation you can use the following;

DateDiffDaysMeasure = DATEDIFF(MAX(Sales[DateKey]), MAX(Sales[EndOfMonth Calendar]), DAY)

Hope this helps!

Have a great day!

[Ety]

Hi Chthonian,

Thanks for precision and to reply on Sunday ;=)
I'm affraid that something escapes me ;=((
When I try to add the measure on the Sales table :
DateDiffDaysMeasure = DATEDIFF(MAX('Sales'[DateKey]);MAX('Sales'[EndOfMonthCalendar]);DAY),  the syntax seems to be incorrect cause 'Sales'[EndOfMonthCalendar] is not a colums on the Sales table...

Sorry the message is in french ;=))

If a delete the MAX function, the syntax is OK but not the result.
 To be sure, I resume what I did :

1. Measure on the Sales Table :
EndOfMonthCalendar = CALCULATE(MAX('Calendar'[DateKey]); FILTER(ALL('Calendar');'Calendar'[DateKey]<=MAX('Calendar'[DateKey])))

2. Measure on the Sales Table :
DateDiffMeasure = DATEDIFF(MAX('Sales'[DateKey]);'Sales'[EndOfMonthCalendar];DAY)

When I filter on SalesKey = 2201, I have blank on each row except in May 2011 where the result is correct.

Have a great day,

Ety

[Ety · Accepted solution]

Hi Chthonian,

I'm not sure that all is perfectly clear in my mind, but I found a solution... I've just delete the relation between the Sales table and the Calendar table and the result becames right.

Before that, when I selected the EofMonthCalendar measure, the system seemed to behave like an SQL cross join and then, for a single SalesKey, I obtained as much rows as months in the Calendar table.

Yes but... No ;=)) A computed column on a Sales table returned data only on one row per SalesKey instead of the same value for all rows with the same SalesKey.

So I tried to delete the relation to force the system to return an SQL cross join with a row context containing data on both tables.

And after that, all what I needed works as I want.

Thank you to help me to resolve the issue. Our discussion was interresting for my understanding mothly because I started Power Bi only a few months ago...

Regards,

Ety.

[Chthonian]

Hi @Ety 

I am a little unsure what you are trying to achieve, I managed to get this working with a simple DATEDIFF, I just ensured that my dates were correctly formatted. Is this the expeced result?

DateDiffDays = DATEDIFF('DateDiff'[DateKey],'DateDiff'[EndOfMonthCalendar],DAY)

 

Apologies if I am not getting something, but just formatting sorted this for me and could easily be used as a measure 😄

[Ety]

Hi Chthonian,

First of all thanks for quick response (I thought I received a mail when someone replies but I didn't receive any notification ;=(() and Happy New Year for you and relatives.

The result you sent to me is exactly what I want, but I'm certainly misunderstanding something because I'm not able to reproduce what you did ;=((

You are speaking about a measure (not calculated column right ?) DateDiffDays that use a table named 'DateDiff' but there's no table with that name in Contenso.  Are you speaking about the Calendar table?

I tried your formula as a measure in the Sales table :

DateDiffDays = DATEDIFF('Sales'[DateKey];'Sales'[EndOfMonthCalendar];DAY)

but it's not correct because 'Sales'[DateKey] is a column and not available like that in a measure.

I tried a calculated column in the Sales table :

DateDiffDays = DATEDIFF('Sales'[DateKey];'Sales'[EndOfMonthCalendar];DAY)

and the result is not yours ;=((

I tried to create the measure EndOfMonthCalendar2 in the Calendar table and I added the following column because a measure seems to be incorrect :

DateDiffDays = DATEDIFF('Sales'[DateKey];'Sales'[EndOfMonthCalendar];DAY)

and the result is always not yours ;=((

I checked that 'Sales'[DateKey], 'Calendar'[DateKey], 'Sales'[EndOfMonthCalendar] and 'Calendar'[EndOfMonthCalendar2] items are correctly set to dates.

Sorry to come back to you. Is it possible that you sent me the sample you did ? I use the January 2019 version of Power BI.

Regards,

Ety.