Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hello i keep getting this message:
Expression.Error: We cannot apply operator - to types DateTime and Date.
Details:
Operator=-
Left=1/1/2024 12:00:00 πμ
Right=1/1/2016
Here is the code at M query editor
let
StartDate = StartDate,
EndDate = Today,
Today = Date.EndOfYear(DateTime.LocalNow()),
Duration = Duration.Days(Duration.From(Today-StartDate))+1,
Dates = List.Dates(StartDate,Duration,#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]), Int64.Type),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "QuarterNo", each Date.QuarterOfYear([Date]), Int64.Type),
#"Added Custom" = Table.AddColumn(#"Inserted Quarter", "Quarter", each "Q"&Text.From([QuarterNo])),
#"Inserted Month" = Table.AddColumn(#"Added Custom", "MonthNo", each Date.Month([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Day" = Table.AddColumn(#"Inserted Month Name", "Day", each Date.Day([Date]), Int64.Type)
in
#"Inserted Day"
Thank u in advance
Solved! Go to Solution.
Yes, the answer is in this line which is dynamic for both start and end (one year back in time (-1), and end of this year:
Source = {Number.From(#date(Date.Year(DateTime.LocalNow()) - 1,01,01))..Number.From(#date(Date.Year(DateTime.LocalNow()),12,31))}
You can set it relative to both start or end (-1 is -1 year).
Or with fixed start and dynamic end:
Source = {Number.From(#date(2016,01,01))..Number.From(#date(Date.Year(DateTime.LocalNow()),12,31))}
Or with fixed start and end:
Source = {Number.From(#date(2016,01,01))..Number.From(#date(2023,12,31))}
Try this code:
let
Source = {Number.From(#date(Date.Year(DateTime.LocalNow()) - 1,01,01))..Number.From(#date(Date.Year(DateTime.LocalNow()),12,31))},
Convert =Table.FromList(Source,Splitter.SplitByNothing(),null,null,ExtraValues.Error),
#"Converted to Table" =Table.TransformColumnTypes(Convert,{{"Column1",type date}}),
#"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]), Int64.Type),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "QuarterNo", each Date.QuarterOfYear([Date]), Int64.Type),
#"Added Custom" = Table.AddColumn(#"Inserted Quarter", "Quarter", each "Q"&Text.From([QuarterNo])),
#"Inserted Month" = Table.AddColumn(#"Added Custom", "MonthNo", each Date.Month([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Day" = Table.AddColumn(#"Inserted Month Name", "Day", each Date.Day([Date]), Int64.Type)
in
#"Inserted Day"
First of all thank u for your response the code worked but the start date is from "1/1/2022" and i want it to start from "1/1/2016".
Any ideas?
Thank you again!
Yes, the answer is in this line which is dynamic for both start and end (one year back in time (-1), and end of this year:
Source = {Number.From(#date(Date.Year(DateTime.LocalNow()) - 1,01,01))..Number.From(#date(Date.Year(DateTime.LocalNow()),12,31))}
You can set it relative to both start or end (-1 is -1 year).
Or with fixed start and dynamic end:
Source = {Number.From(#date(2016,01,01))..Number.From(#date(Date.Year(DateTime.LocalNow()),12,31))}
Or with fixed start and end:
Source = {Number.From(#date(2016,01,01))..Number.From(#date(2023,12,31))}
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
80 | |
79 | |
58 | |
36 | |
35 |
User | Count |
---|---|
99 | |
56 | |
56 | |
46 | |
40 |