Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello All,
I have three tables and their relashionships looks like this.
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))
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
Solved! Go to Solution.
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.
Thanks,
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.
Thanks,
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |