select data from one table with date range from another

[Anonymous]

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

[Cmcmahan]

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.

[Anonymous]

Hi

and thanks for your help.

I already had this idea of creating a date table, I did it and linked it to my 2 tables. But as my skills and knowledge of database and Power BI are poor, I was stuck and didn't know how to use it/filter it. So if you have any example or tutorial I would appreciate

thanks

Regards

[v-chuncz-msft · Accepted solution]

@Anonymous 

You may refer to the post below.

https://community.powerbi.com/t5/Desktop/Use-a-date-slicer-to-filter-on-a-period-instead-of-a-single...