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
Anonymous
Not applicable

Get Dates between Start Date And End Date from another table

Hello All,

 

I have three tables and their relashionships looks like this.

 

Capture.JPG

 

Date is master table which having dates from 1st may to 31st may.

PowerQuery Code :- 

 

let
    StartDate = #date(2018,5,1),
    EndDate = #date(2018,5,31),
    NumberOfDays = Duration.Days( EndDate - StartDate ),
    Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-IN")
in
    #"Changed Type with Locale"

To select a date range that is start date and end date with time, i have created table as fallows,

 

Timerange:- 

let
    Source = List.Times(#time(0,0,0) , 1440, #duration(0,0,30,0)),
    convertToTable = Table.FromList(Source, Splitter.SplitByNothing(), {"TimeRange"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(convertToTable,{{"TimeRange", type time}})
in
    #"Changed Type"

Date-Time Range Table

 

let
    StartDate = #date(2018,5,1),
    EndDate = #date(2018,5,31),
    NumberOfDays = Duration.Days( EndDate - StartDate ),
    Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-IN"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each TimeRange),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"TimeRange"}, {"TimeRange"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"TimeRange", type time}})
    
in
    #"Changed Type1"

Now in Date-Range table i have created two calculated columns where it gives user selected Date-Time Range as below.

 

Start Date = 
VAR maxDate = CALCULATE(MAX('Date'[Date]),ALLEXCEPT('Date','Date'[Date]))
VAR maxTime = CALCULATE(MAX('DateRange'[TimeRange]),ALLEXCEPT('DateRange','DateRange'[Date],DateRange[TimeRange]))
RETURN
    maxDate&" "&maxTime
End Date = ((DateRange[Start Date]+1)-TIME(0,0,1))

Capture2.JPG

 Now i would like to get the Dates between these start date and end dates from May tables Date-Time Column.

 

How can i get this done. 

Please help me.

 

Sample Data:- https://1drv.ms/x/s!AhiQ2f7YQHC-gbNZLawDDsorPpzKaQ

 

Please 

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I tried the below steps and it worked for me.

 

1. Break relationship from date range table to 'May' table.

2. Write a measure to check current selection from date range table and return tag.

Is Range =
VAR currDate =
    MAX ( May[Date-Time] )
VAR _start =
    MAX ( DateRange[SelectedDate-Time] )
VAR _end =
    MAX ( DateRange[NextDate] )
RETURN
    IF ( currDate >= _start && currDate <= _end, "Y", "N" )

 

3. Create table visual based on 'May' table, drag above measure to visual level filter to filter matched records.

19.PNG

 

38.gif

 

Thanks,

View solution in original post

1 REPLY 1
Anonymous
Not applicable

I tried the below steps and it worked for me.

 

1. Break relationship from date range table to 'May' table.

2. Write a measure to check current selection from date range table and return tag.

Is Range =
VAR currDate =
    MAX ( May[Date-Time] )
VAR _start =
    MAX ( DateRange[SelectedDate-Time] )
VAR _end =
    MAX ( DateRange[NextDate] )
RETURN
    IF ( currDate >= _start && currDate <= _end, "Y", "N" )

 

3. Create table visual based on 'May' table, drag above measure to visual level filter to filter matched records.

19.PNG

 

38.gif

 

Thanks,

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.