Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@Anonymous
You may refer to the post below.
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.
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
@Anonymous
You may refer to the post below.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |