Dates Table is not working

DAX DAX Calculations
1

For some reason, my dates table is not working. I have tried to troubleshoot without success. The file is attached. Amy thoughts?Pipedrive Deals.pbix (435.0 KB)

2

your date table is 2014-2016, but your data is form 2019-2020

3

Use Deals[Deal created] instead of Dates[Date] in your visual.

4

Thank you, Heather!

5

You are quite welcome @jmolina
at some point, you may want to consider a date table that lets you make use of the max and min dates in your dataset.

6

Do you have the code for that? I can easily switch them out.

7

My code actually handles the dates outside of the calendar table - feeds them in as parameters.

so, build a blank query - and type this into the bar (modify for your dataset) to grab the Max date:
= Date.Day(List.Max(USAGE[Posting Date])
call this query “EndDate”

Next, build a blank query and type this into the bar:
= Date.AddYears (Date.StartOfYear (EndDate), -2)
call this query “StartDate” (you could also modify to use List.Min if you like)

I’ll plug the calendar into the next reply

8

@Heather do you have the code for that table?

9

Calendar Table code:

let
#"==Build Date Column==" =CurrentDate,
ListDates = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(1,0,0,0)),
#“Converted to Table” = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#“Renamed Columns as Date” = Table.RenameColumns(#“Converted to Table”,{{“Column1”, “Date”}}),
// As far as Power BI is concerned, the ‘Date’ column is all that is needed :slight_smile: But we will continue and add a few Human-Friendly Columns
#“Changed Type to Date” = Table.TransformColumnTypes(#“Renamed Columns as Date”,{{“Date”, type date}}),
#"==Add Calendar Columns==" = #“Changed Type to Date”,
#“Added Calendar MonthNum” = Table.AddColumn(#"==Add Calendar Columns==", “MonthNum”, each Date.Month([Date]), Int64.Type),
#“Added Month Name Long” = Table.AddColumn(#“Added Calendar MonthNum”, “MonthLong”, each Date.MonthName([Date]), type text),
#“Added Month” = Table.AddColumn(#“Added Month Name Long”, “Month”, each Text.Start([MonthLong], 3), type text),
#“Added Calendar Quarter” = Table.AddColumn(#“Added Month”, “Quarter”, each “Q” & Text.From(Date.QuarterOfYear([Date]))),
#“Added QtrNum” = Table.AddColumn(#“Added Calendar Quarter”, “QtrNum”, each Date.QuarterOfYear([Date])),
#“Added Calendar Year” = Table.AddColumn(#“Added QtrNum”, “Year”, each Date.Year([Date]), Int64.Type),
#"==Add Calendar Date Offset Columns==" = #“Added Calendar Year”,
// Can be used to for example to show the past 3 months(CurMonthOffset = 0, -1, -2)
#“Added CurMonthOffset” = Table.AddColumn(#"==Add Calendar Date Offset Columns==", “CurMonthOffset”, each ( Date.Year([Date]) - Date.Year(CurrentDate) ) * 12+ Date.Month([Date]) - Date.Month(CurrentDate), Int64.Type),
// Can be used to for example to show the past 3 quarters (CurQuarterOffset = 0, -1, -2)
#“Added CurQuarterOffset” = Table.AddColumn(#“Added CurMonthOffset”, “CurQuarterOffset”, each /Year Difference/
( Date.Year([Date]) - Date.Year(CurrentDate) )*4
/Quarter Difference/
+ Number.RoundUp(Date.Month([Date]) / 3)
- Number.RoundUp(Date.Month(CurrentDate) / 3),
Int64.Type),
// Can be used to for example to show the past 3 years (CurYearOffset = 0, -1, -2)
#“Added CurYearOffset” = Table.AddColumn(#“Added CurQuarterOffset”, “CurYearOffset”, each Date.Year([Date]) - Date.Year(CurrentDate), Int64.Type),
#"==Add General Columns==" = #“Added CurYearOffset”,
// Used as ‘Sort by Column’ for MonthYear columns
#“Added MonthYearNum” = Table.AddColumn(#"==Add General Columns==", “MonthYearNum”, each [Year]*100 + [MonthNum] /e.g. Sep-2016 would become 201609/, Int64.Type),
#“Added MonthYear” = Table.AddColumn(#“Added MonthYearNum”, “MonthYear”, each [Month] & “-” & Text.End(Text.From([Year]),2)),
#“Added MonthYearLong” = Table.AddColumn(#“Added MonthYear”, “MonthYearLong”, each [Month] & “-” & Text.From([Year])),
#“Added QtrYear” = Table.AddColumn(#“Added MonthYearLong”, “QtrYear”, each [Quarter] & " " & Text.From([Year])),
#“Added QtrYrNum” = Table.AddColumn(#“Added QtrYear”, “QtrYrNum”, each [Year]*100+[QtrNum], Int64.Type),
#“Added WeekdayNum” = Table.AddColumn(#“Added QtrYrNum”, “WeekdayNum”, each Date.DayOfWeek([Date]), Int64.Type),
#“Added Weekday Name” = Table.AddColumn(#“Added WeekdayNum”, “Weekday”, each Text.Start(Date.DayOfWeekName([Date]),3), type text),
#“Added WeekdayWeekend” = Table.AddColumn(#“Added Weekday Name”, “WeekdayWeekend”, each if [WeekdayNum] = 0 or [WeekdayNum] = 6
then “Weekend”
else “Weekday”),
#“Duplicated Column” = Table.DuplicateColumn(#“Added WeekdayWeekend”, “Year”, “Year Text”),
#“Changed Type” = Table.TransformColumnTypes(#“Duplicated Column”,{{“Year Text”, type text}}),
#“Uppercased Text” = Table.TransformColumns(#“Changed Type”,{{“Month”, Text.Upper, type text}})
in
#“Uppercased Text”

10

Disclaimer, I did not design this complete table - I ‘stole’ most of it from Avi Singh’s “Ultimate Calendar Table” that he publishes frequently with his YouTube Webinars

11

Here is another set of M code that gives you a bit more information than the one provided. There are many different ways to set this up. Attached, is a copy of the code that I use. @Melissa played a major part in helping develop this. Also, when asking questions in the forum, please do a search for that topic before posting. There are numerous threads in the forum regarding the setup of a proper Date Table.

Power BI Date Table Code with Offsets - 4-6-2020.txt (6.8 KB)

Thanks
Jarrett

2 Likes