Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin 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))}
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
63 | |
59 | |
56 | |
38 | |
29 |
User | Count |
---|---|
82 | |
62 | |
45 | |
41 | |
40 |