Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Ghaston
Helper I
Helper I

Creating Date/Time calendar

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 ! 

1 ACCEPTED 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].

1.PNG

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-danhe-msft
Microsoft Employee
Microsoft Employee

Hi @Ghaston,

Based on my test, you could refer to below steps:

Sample data:

1.PNG

Find the Earliest value in table A, the Latest value in Table B and convert to table.

3.PNG4.PNG

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

 

5.PNG

6.PNG

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 ) 
1.PNG


My date/Time columns, don't contain seconds. Maybe that's the problem ? 
2.PNG

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.

1.PNG

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.  
1.PNG2.PNG3.PNG 

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

1.PNG

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-danhe-msft, thanks ! 
it worked perfectly 
thanks for your time 🙂 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.