Efficient Date-Time Table Creation Using DAX

[cnesr]

Hello,

Super new Power BI user. Thanks for the help and apologies for the new guy questions.

I am looking to create a Date-Time table by hour (automatically updates based on new data added to the fact table is a bonus).

I have been looking around but I can't find a nice solution for this. The closest thing I have so far (which is NOT hourly) is the following:

Dates =
VAR BaseCalendar =
    CALENDARAUTO()
RETURN
    GENERATE (
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR YearDate = YEAR ( BaseDate )
        VAR MonthNumber = MONTH ( BaseDate )
        RETRUN ROW (
            "Day", BaseDate,
            "Year", YearDate,
            "Month Number", MonthNumber,
            "Month", FORMAT ( BaseDate, "mmm" ),
            "Year Month", FORMAT ( BaseDate, "mmm yy" )
        )
    )

If any light can be shed on the following as well, that would be really appreciated:

• Why exactly do Date-Time tables work better than dates imported with fact tables?
• After the Date-Time table is created, I need to link it to the date-time column in my fact table?
• After the linking is complete, what would be the best way to find nested averages/sums? For example:
  • I have vehicle volume counts by hour in the fact table. I want to (1) average the (for example) 9:00AM to 10:00AM vehicle counts for Mondays in June 2018, (2) then do this for every hour of those Mondays, and (3) then sum up those 24 1-hour averages and call that the average Monday in June 2018. I need to do this for each weekday of each month of each year. The weekday averages get averaged out (weighted) to form monthly averages. The monthly averages get averaged out to form yearly averages... I need to report on daily averages, monthly, and yearly. Can anyone provide a method of attack for this...? =D

Thank you all very, very much!

[Seward12533 · Accepted solution]

Here is a link to a previous discusson on creating the Date Time Table.

http://community.powerbi.com/t5/Desktop/Multi-level-aggregation-using-DAX-measures/m-p/466016#M21638...

Don't really have the time to answer all your questions right now but the short verison is the time Intelligence features of DAX are amazing and it handles most of the filter contriants and aggregation for you.

Vehicle Volume on Mondays for the 10 Oclock Hour = CALCULATE(SUM(Counts),date[day]="Monday"&&Date[hour]=10)

Just Drop this measure in a chart and add Months or any other field form your date table on the axis and BOOM!!

Good Luck.

[cnesr]

Thanks again, @Seward12533!!!