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.
Hello All,
I am using the following dax formula for the creation of the custom calendar
= Source(#date(2017, 01, 01), Duration.Days(#date(2018, 12, 31) - #date(2017, 01, 01))+1, #duration(1, 0, 0, 0))
I would like to replace the end date (#date(2018,12,31) with today's date and i want every time the calendar runs to get today's date as end date.
How can i add today's date in the formula above.
Thanks
Solved! Go to Solution.
This is M language not DAX.
in M you can get today using
DateTime.LocalNow()
https://msdn.microsoft.com/en-us/query-bi/m/datetime-localnow
I use this function for my date dimension from @mattmasson
https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/
and pass dates. Setting end date to this will create dates up to today.
= DateFunction(StartDate, Date.AddDays(DateTime.Date(DateTime.LocalNow()),1), null)
Hi @themistoklis,
It seems that you are fairly new to Power BI.
The formula you posted is not that of DAX but of M. If you want to create a calendar in DAX, you may use the CALENDAR() function. Example:
CALENDAR TABLE = CALENDAR ( "1/1/2018", TODAY () )
The above formula should be created as a table and not a measure or a calculated column.
In M, you will have to create a list of dates first then convert it to a table. An example nested formula that generates a list of dates then converts the list to a table.
= Table.FromList( List.Dates( #date(2018, 1, 1), Number.From(DateTime.Date(DateTime.LocalNow())- #date(2018, 1, 1)) + 1, #duration(1, 0, 0, 0)), Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error)
From Get Data find Blank Query and then place the formula above into the formula bar. If the formula bar is not visible, go to View tab of Power Query Editor and then tick the checkbox for Formula Bar.
Proud to be a Super User!
Indeed im quite new to PowerBI.
Thank you both for the formulas that you sent. They both work great!
My working formula looks like this now:
= Source(#date(2017, 01, 01), Number.From(DateTime.Date(DateTime.LocalNow()) - #date(2017, 01, 01))+1, #duration(1, 0, 0, 0))
Hi @themistoklis,
Source is not a M function. Change your formula to
= List.Dates(#date(2017, 01, 01), Number.From(DateTime.Date(DateTime.LocalNow()) - #date(2017, 01, 01))+1, #duration(1, 0, 0, 0))
Then convert the generated list to a table.
Proud to be a Super User!
Source is actually the name of the previous applied step.
And this step has the fucntion that you mentioned (= List.Dates)
Thanks for clarifying it
Hi @themistoklis,
It seems that you are fairly new to Power BI.
The formula you posted is not that of DAX but of M. If you want to create a calendar in DAX, you may use the CALENDAR() function. Example:
CALENDAR TABLE = CALENDAR ( "1/1/2018", TODAY () )
The above formula should be created as a table and not a measure or a calculated column.
In M, you will have to create a list of dates first then convert it to a table. An example nested formula that generates a list of dates then converts the list to a table.
= Table.FromList( List.Dates( #date(2018, 1, 1), Number.From(DateTime.Date(DateTime.LocalNow())- #date(2018, 1, 1)) + 1, #duration(1, 0, 0, 0)), Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error)
From Get Data find Blank Query and then place the formula above into the formula bar. If the formula bar is not visible, go to View tab of Power Query Editor and then tick the checkbox for Formula Bar.
Proud to be a Super User!
This is M language not DAX.
in M you can get today using
DateTime.LocalNow()
https://msdn.microsoft.com/en-us/query-bi/m/datetime-localnow
I use this function for my date dimension from @mattmasson
https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/
and pass dates. Setting end date to this will create dates up to today.
= DateFunction(StartDate, Date.AddDays(DateTime.Date(DateTime.LocalNow()),1), null)
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |