Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

select data from one table with date range from another

Hi guys

I am production manager in a chemical factory and trying to use power BI for some reports. Here is my problem :

I have a first table with :

run number       start date     end date

 

and a second table containing all my production records

date     tag     value

 

I want to select a run number, and display only data from the second table that are between start date and end date. I tried different things but none of them worked yet...

any idea ??

 

thanks for your help

regards

michael

Need Help

3 Replies

  • Cmcmahan's avatar
    Cmcmahan
    Resident Rockstar
    7 years ago

    The easiest and best solution is to create a Date Dimension table!  Then you relate the date dimension to your production record date field and filter the date dimension based on dates between your start and end run dates.

     

    Here's the power query I use to create a basic date dimension.  Be sure to update the StartDate and EndDate as appropriate:

    let
    StartDate = #date(2019,1,1),
    EndDate = #date(2019,12,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}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), type number),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), type number),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([Date]), type number),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([Date]), type number),
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date]), type number),
    #"Inserted Day" = Table.AddColumn(#"Inserted Week of Month", "Day", each Date.Day([Date]), type number),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([Date]), type number),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([Date]), type number),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Year", "Day Name", each Date.DayOfWeekName([Date]), type text)
in
    #"Inserted Day Name"

    If you have further questions, feel free to follow up here.