Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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!
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
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.
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 😄
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
73 | |
50 | |
45 | |
20 | |
17 |