The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi guys !
I have a date/time column " Date_Heure" dans la table "SGD_CPT_LIGNE_LIVE_VUE" and another one that has the same name in "SGD_CPT_LIGNE_HISTO_VUE".
I want to create a date/time table, wich takes the min date/time from "SGD_CPT_LIGNE_HISTO_VUE", and the last date/time from "SGD_CPT_LIGNE_LIVE_VUE".
So i created a new table, using this DAX Expression :
Date = CALENDAR ( MINX ("SGD_CPT_LIGNE_LIVE_VUE";"SGD_CPT_LIGNE_LIVE_VUE"[Date_Heure]) ; MAXX ("SGD_CPT_LIGNE_HISTO_VUE";"SGD_CPT_HISTO_LIVE_VUE"[Date_Heure]) )
But i get a column with dates only! ( no time ) The time is always 00:00:00.
I need to create a date/time table where time increses per 1 minute. ( 13:10 => 13:11 => 13:12 ...)
Please help me out !
Solved! Go to Solution.
Hi @Ghaston,
Please check the code in the advanced language like the picture below:
The duration should from a smaller value, so you need modify the code from the B{0}[Column1].
Regards,
Daniel He
Hi @Ghaston,
Based on my test, you could refer to below steps:
Sample data:
Find the Earliest value in table A, the Latest value in Table B and convert to table.
Create a new blank query and enter the code in the advanced language:
let Source =List.DateTimes( A{0}[Column1],DayCount,#duration (0,0,1,0)), DayCount = Duration.TotalMinutes(Duration.From (B{0}[Column1] - A{0}[Column1]))+1,TableFromList = Table.FromList(Source, Splitter.SplitByNothing()), ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type datetime}}) in ChangedType
Now you could get the correct result.
You can also download the PBIX file to have a view.
https://www.dropbox.com/s/3429u4vy08vvlp7/Time%20duration.pbix?dl=0
Regards,
Daniel He
Hi @v-danhe-msft
Thanks for your detailed comment.
I Followed your instructions, but i get an error at the end. ( "increment" is out of limit )
My date/Time columns, don't contain seconds. Maybe that's the problem ?
Hi @Ghaston,
I have reproduecd your problem. You could check the code in the advanced language, when you are using the Duration.from function, the parameter should be a bigger one to minus a smaller one, see the picture post below, if you used a smaller one to minus the bigger one, it will show the same error, I suggest to check the value and modify the code.
Regards,
Daniel He
Hi @v-danhe-msft
you were right! i had to modify the code like you showed me.
I don't get that error now, but instead, i get a calendar that starts from my bigger column (date/time in column A) , to 2021 !
I verified my A and B tables, and they are fine.
Hi @Ghaston,
Please check the code in the advanced language like the picture below:
The duration should from a smaller value, so you need modify the code from the B{0}[Column1].
Regards,
Daniel He
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
137 | |
106 | |
105 | |
73 | |
59 |
User | Count |
---|---|
266 | |
127 | |
119 | |
100 | |
88 |