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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PBISändy
Frequent Visitor

Find minimum timestamp within one day with DAX

Hi PBI Community,

 

i have the following problem and i really don't know how to solve it:

I have for example a following table with processes:

 

processstartend
122.01.2018 23:0022.01.2018 13:00
223.01.2018 00:0022.01.2018 14:00
323.01.2018 01:0022.01.2018 15:00
423.01.2018 02:0022.01.2018 13:00
123.01.2018 23:0023.01.2018 13:00
224.01.2018 00:0023.01.2018 14:00
324.01.2018 01:00

23.01.2018 15:00

 

424.01.2018 02:0023.01.2018 13:00

 

- Each of them has start and end time

 

What I would like to know out of this data with DAX: 

For each specific day f.e. the 22.01 what was the minimum start timestamp on that day and what was the max end timestamp for one day? And another assumption each process has a attribut like region attached than my question woul be: what is the minimum starting time for each process per day per region.

 

I hope you can help me! Thanks a lot and a great day to all of you

1 ACCEPTED SOLUTION

Hi,

 

>>> For each specific day f.e. the 22.01 what was the minimum start timestamp on that day and what was the max end timestamp for one day?

To accomplish this, you could split the column “start” into “startdate” and “starttime”, similarly, split “end” into “enddate” and “endtime”. You can do this in “Edit Query”>> Split Column >> by number of characters (10) .

 

Then add two measures :

min_timestamp_in_one_day = CALCULATE(MINX(Table1,Table1[starttime]),ALLEXCEPT(Table1,Table1[startdate]))

 

max_timestamp_in_one_day = CALCULATE(MAXX(Table1,Table1[endtime]),ALLEXCEPT(Table1,Table1[enddate]))

 

Noted you need to manually change the datatype format for all the relative columns and above two measures.

 

1.PNG

Then you will get result like:

 

2.PNG

>>> what is the minimum starting time for each process per day per region.

 

So what result would you like to see if we are dealing with the table you post here?

 

For example, I have a datasource like:

 

3.PNG

If you want to calculate the earliest timestamp for a region,  you could use DAX like:

 

Earliest_time_per_region = CALCULATE(MINX(Table1,Table1[starttime]),ALLEXCEPT(Table1,Table1[Region]))

 

And you could add the column in the “ALLEXCEPT” function as you wish. For example, you could also use:

 

Earliest_time_per_region_per_day = CALCULATE(MINX(Table1,Table1[starttime]),ALLEXCEPT(Table1,Table1[Region]),Table1[startdate])

 

BR,

Henry 

 

 

View solution in original post

3 REPLIES 3
PBISändy
Frequent Visitor

Hi PBI Community,

 

i have the following problem. Lets assume following table of processes with start and end times:

 

processstartend
122.01.2018 23:0022.01.2018 13:00
223.01.2018 00:0022.01.2018 14:00
323.01.2018 01:0022.01.2018 15:00
423.01.2018 02:0022.01.2018 13:00
123.01.2018 23:0023.01.2018 13:00
224.01.2018 00:0023.01.2018 14:00
324.01.2018 01:0023.01.2018 15:00
424.01.2018 02:0023.01.2018 13:00

 

What I would like to know out of this table is for each day the minimum start time of a process and for each day the maximum end timestamp of a process. Lets assume also another attribute called region on each process. What is the minum starting time per day for each region?

 

I hope someone can help me.

 

Thanks,

Sandra

Hi,

 

>>> For each specific day f.e. the 22.01 what was the minimum start timestamp on that day and what was the max end timestamp for one day?

To accomplish this, you could split the column “start” into “startdate” and “starttime”, similarly, split “end” into “enddate” and “endtime”. You can do this in “Edit Query”>> Split Column >> by number of characters (10) .

 

Then add two measures :

min_timestamp_in_one_day = CALCULATE(MINX(Table1,Table1[starttime]),ALLEXCEPT(Table1,Table1[startdate]))

 

max_timestamp_in_one_day = CALCULATE(MAXX(Table1,Table1[endtime]),ALLEXCEPT(Table1,Table1[enddate]))

 

Noted you need to manually change the datatype format for all the relative columns and above two measures.

 

1.PNG

Then you will get result like:

 

2.PNG

>>> what is the minimum starting time for each process per day per region.

 

So what result would you like to see if we are dealing with the table you post here?

 

For example, I have a datasource like:

 

3.PNG

If you want to calculate the earliest timestamp for a region,  you could use DAX like:

 

Earliest_time_per_region = CALCULATE(MINX(Table1,Table1[starttime]),ALLEXCEPT(Table1,Table1[Region]))

 

And you could add the column in the “ALLEXCEPT” function as you wish. For example, you could also use:

 

Earliest_time_per_region_per_day = CALCULATE(MINX(Table1,Table1[starttime]),ALLEXCEPT(Table1,Table1[Region]),Table1[startdate])

 

BR,

Henry 

 

 

Thanks a lot!! 🙂 That's exactly what I needed 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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