Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
commited2020
Frequent Visitor

Date Error in M query editor

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

1 ACCEPTED 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))}

 

View solution in original post

3 REPLIES 3
tt_and
Helper I
Helper I

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))}

 

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.