Solved: Dax Query with date variables

Anonymous · ‎08-12-2022

Trying to insert some rows into my records without adding a new source. Mostly got it working but it would be nice if there was a predefined date variable I could use to get the current year, month, month day.

Rigth now I am doing this...

#"Insert Blanks" = Table.InsertRows(#"Renamed Columns", 0,{[Date = "1/1/2022" , Value = 0, Name= "xraphael"]})

I want to do something like this...

#"Insert Blanks" = Table.InsertRows(#"Renamed Columns", 0,{[Date = FORMAT("01/01/" & YEAR(TODAY()), "MM/DD/YYYY"), Value = 0, Name= "xraphael"]})

I dont know the correct syntax

Vijay_A_Verma · ‎08-12-2022

You can use this to insert 01-01-2022

= Table.InsertRows(#"Renamed Columns", 0,{[Date = Date.ToText(Date.StartOfYear(Date.From(DateTime.FixedLocalNow())),"MM/dd/yyyy") , Value = 0, Name= "xraphael"]})

If you want to use the first date of month, then replace Date.StartOfYear with Date.StartOfMonth.

If you want current date, then remove Date.StartOfYear.

View solution in original post

Anonymous · ‎08-12-2022

Thanks this works for me

Vijay_A_Verma · ‎08-12-2022

You can use this to insert 01-01-2022

= Table.InsertRows(#"Renamed Columns", 0,{[Date = Date.ToText(Date.StartOfYear(Date.From(DateTime.FixedLocalNow())),"MM/dd/yyyy") , Value = 0, Name= "xraphael"]})

If you want to use the first date of month, then replace Date.StartOfYear with Date.StartOfMonth.

If you want current date, then remove Date.StartOfYear.