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.
Hi Guys,
I need to create a Dynamic calendar table that will have just the dates from the fist day of the year today(-1) for example today is 25th of October 2018 I would like the table to have all dates from.
1st of January 2015 – 24th of October 2015
1st of January 2016 – 24th of October 2016
1st of January 2017 – 24th of October 2017
1st of January 2018 – 24th of October 2018
I would like to create the same dynamic date table for months. For example, if we are on 25th of October 2018. I would like the dynamic month date table to have all dates between.
1st of October 2015 – 24th of October 2015
1st of October 2016 – 24th of October 2016
1st of October 2017 – 24th of October 2017
1st of October 2018 – 24th of October 2018
I found this code here on https://powerbi.tips/2017/11/creating-a-dax-calendar/ That does almost what I wanted but it creates a table date with all dates from 25th of October 2018 until 25th of October 2015, including for example 26th of October – 31st of December of (2017, 2016, 2015) I needed to exclude that.
Here is the code I am using to create the table.
Dates 5 = GENERATE ( CALENDAR( DATE( YEAR( TODAY() ) - 5, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()), VAR startOfWeek = 1 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday VAR currentDay = [Date] VAR days = DAY( currentDay ) VAR months = MONTH ( currentDay ) VAR years = YEAR ( currentDay ) VAR nowYear = YEAR( TODAY() ) VAR nowMonth = MONTH( TODAY() ) VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1 VAR todayNum = WEEKDAY( TODAY() ) VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) ) RETURN ROW ( "day", days, "month", months, "year", years, "day index", dayIndex, "week index", weekIndex, "month index", INT( (years - nowYear ) * 12 + months - nowMonth ), "year index", INT( years - nowYear ) ) )
Unfortunately the Power Query/DAX knowledge that I have are not enough to crack this problem. Any help would be very appreciated.
Thanks in Advance
Solved! Go to Solution.
For this scenario I'd rather use Power Query:
Query 1 :
let YearsBack = 5, TodayDate = Date.From( DateTime.LocalNow() ), YearsList = {0..YearsBack}, Calendar = List.Accumulate( YearsList, #table({},{}), (state, current) => let FirstDayOfYear = Date.StartOfYear( Date.AddYears( TodayDate, - current ) ), LastDay = Date.AddDays( Date.AddYears( TodayDate, - current ), -1 ), DatesList = List.Transform( List.Dates( FirstDayOfYear, 1 + Duration.TotalDays( LastDay - FirstDayOfYear ), #duration(1, 0, 0, 0) ), each {_} ), DateTable = #table( type table [Date = Date.Type], DatesList ) in Table.Combine( {state, DateTable}) ) in Calendar
Query 2:
let YearsBack = 5, TodayDate = Date.From( DateTime.LocalNow() ), YearsList = {0..YearsBack}, Calendar = List.Accumulate( YearsList, #table({},{}), (state, current) => let FirstDayOfYear = Date.StartOfMonth( Date.AddYears( TodayDate, - current ) ), LastDay = Date.AddDays( Date.AddYears( TodayDate, - current ), -1 ), DatesList = List.Transform( List.Dates( FirstDayOfYear, 1 + Duration.TotalDays( LastDay - FirstDayOfYear ), #duration(1, 0, 0, 0) ), each {_} ), DateTable = #table( type table [Date = Date.Type], DatesList ) in Table.Combine( {state, DateTable}) ) in Calendar
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
For this scenario I'd rather use Power Query:
Query 1 :
let YearsBack = 5, TodayDate = Date.From( DateTime.LocalNow() ), YearsList = {0..YearsBack}, Calendar = List.Accumulate( YearsList, #table({},{}), (state, current) => let FirstDayOfYear = Date.StartOfYear( Date.AddYears( TodayDate, - current ) ), LastDay = Date.AddDays( Date.AddYears( TodayDate, - current ), -1 ), DatesList = List.Transform( List.Dates( FirstDayOfYear, 1 + Duration.TotalDays( LastDay - FirstDayOfYear ), #duration(1, 0, 0, 0) ), each {_} ), DateTable = #table( type table [Date = Date.Type], DatesList ) in Table.Combine( {state, DateTable}) ) in Calendar
Query 2:
let YearsBack = 5, TodayDate = Date.From( DateTime.LocalNow() ), YearsList = {0..YearsBack}, Calendar = List.Accumulate( YearsList, #table({},{}), (state, current) => let FirstDayOfYear = Date.StartOfMonth( Date.AddYears( TodayDate, - current ) ), LastDay = Date.AddDays( Date.AddYears( TodayDate, - current ), -1 ), DatesList = List.Transform( List.Dates( FirstDayOfYear, 1 + Duration.TotalDays( LastDay - FirstDayOfYear ), #duration(1, 0, 0, 0) ), each {_} ), DateTable = #table( type table [Date = Date.Type], DatesList ) in Table.Combine( {state, DateTable}) ) in Calendar
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Thanks for your solution it is really helped - One more thing:
If I need to a previous year full dates how I can do that.
example: 2019 UTD 6-Aug and 2018 UTD 6-AUG --- I need 2019 UTD 6-AUG and 2018 full days
Hi Livio,
I just saw this post and I was wondering if there was a way to rewrite this formula to show 12 months back from current date instead of years. I tried replacing the "Year" with "Month" but I received an error message. I have been working on a query for a while and your formula is the closest that I have found to creating the query that I need.
Any help would be appreciated. Thank you in advance.
Regards,
Angela
It worked you are a genius!!!!!
That’s perfect to do YTD and MTD measures. Because I was having trouble with TOTALYTD measure because all it does is accumulate the months but I did not want to get the whole month, so figured I had to create a new date data set, and it worked!!!
Thank you once again!
Could you point the way what are the PowerQuery commands to do the same as I was doing before?
Because the only output I get from the query that you´ve made are the dates, but it would be nice to also have
VAR days = DAY( currentDay )
VAR months = MONTH ( currentDay )
VAR years = YEAR ( currentDay )
VAR nowYear = YEAR( TODAY() )
VAR nowMonth = MONTH( TODAY() )
VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1
VAR todayNum = WEEKDAY( TODAY() )
VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )
RETURN ROW (
But I guess I can create this on power query editor the extra columns…
Regards,
Tiago
Hi @tiago
You can add extra steps to the query via the UI. Just go to the Add Column tab and go to the From Date & Time section
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
I have similar problem, would love some input on this! Thx for elaborating the question.
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |