Reply
Alexandra_B
Helper III
Helper III
Partially syndicated - Outbound

Select specific date from table

Hello there.


I have a column in my query field that is Date type. How could I create another column to be able to take selected date from first date table into the new column. For example I have date range 01/01/2021 - 30/07/2022. I want to be able to select School time off / half term/ holidays (01/02/09/2021, 25/10/2021-29/10/2021, etc.) If I can do it using measures and not creating a column, that`s ok too, as long as I am able to add multiple date periods.

 

Thank you

1 ACCEPTED SOLUTION

Syndicated - Outbound

@Alexandra_B , You can use date table and time filter or both date and time table as filter. Assume date filter is all sorted

 

you can add time filter on a time column (have or create like)

 

Time = timevalue([Datetime])

 

calculate(Sum(Table[Value]) , filter(Table, Table[Time]>= time(8,0,0)  && Table[time] >= time(16,0,0) ) )

 

or use time table to

 

https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

7 REPLIES 7
Alexandra_B
Helper III
Helper III

Syndicated - Outbound

I have sorted it out.

Because I didn`t know how to create a table with specific date from another table, I just added two tables (Home - Enter data - table for School term, same for Half term).

Then, I went to Model to create relationship 1-* (see attached).

After that, I created the visuals I needed, with a slicer representing Days of the week, number of days and other required data. (Please note this is just from the data source I have and it is not complete, if you are checking the count of days 😁 )

I still have to work on design and edit some visuals, but at least I reached the main objective.

Thank you @amitchandak for resources!

 

have a wonderful week ahead!

1-* relationship1-* relationshipHalf termHalf termSchool termSchool term

Alexandra_B
Helper III
Helper III

Syndicated - Outbound

I was thinking maybe I create another table and create a relationship with the main Date table. It is possible to add multiple date range in the new table?

I now I can do like this :

New Date table = calendar(date(25/10/2021), date(29/10/2021))

but can I add in the same calendar  more dates like 20/12/2021 - 04/01/2022, etc?

 

25/10/2021
26/10/2021
27/10/2021
28/10/2021
29/10/2021
20/12/2021
21/12/2021
22/12/2021
04/01/2022
04/04/2022

 

It is possible to create something like this , then create relationship with the main Date column?

Thank you.

amitchandak
Super User
Super User

Syndicated - Outbound

@Alexandra_B ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

refer if this can help

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Syndicated - Outbound

Basically I need to calculate data from half hourly fields based on date.
I have created a slicer for days of the week, I will need to calculate the data between 08:00 - 16:00 Mon to Fri for specific dates (when school is open) and then calcuate a different time for the rest of the time.

To calculate the data from 08-16:00 for example I can create a measure to sum the fields, then I can select multiple days from the slicer. I woud like to be somehow able to set another slicer/ measure/ column withdates when schools is open.

Syndicated - Outbound

@Alexandra_B , You can use date table and time filter or both date and time table as filter. Assume date filter is all sorted

 

you can add time filter on a time column (have or create like)

 

Time = timevalue([Datetime])

 

calculate(Sum(Table[Value]) , filter(Table, Table[Time]>= time(8,0,0)  && Table[time] >= time(16,0,0) ) )

 

or use time table to

 

https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Syndicated - Outbound

Date filter.JPG

 

Syndicated - Outbound

@Alexandra_B . Sorry not clear to me

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)