[HELP] How to create a Dynamic calendar table from 1st of January until today(-1) for all years

[tiago]

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

[LivioLanzo · Accepted 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

[Hesham]

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 

[Angela_Rom]

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

[tiago]

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

[LivioLanzo]

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

[StevenKiser]

I have similar problem, would love some input on this! Thx for elaborating the question.