Forum Discussion
How to create a Continuous custom Date Hierarchy?
Then automatic date hierarchy created by Power BI is always continuous meaning that if you drill down to month-level in a line chart you can still use e.g. the Forecasting-feature. But when I create a date hierarchy on my own I can't manage to keep it continuous when drilling down. Instead it automatically switches to Categorical, and then e.g. Forecasting won't work. I've tried all kinds of different ways of creating the hierarchy (e.g. using "Month" = MONTH( [Date] ) or "Month" = FORMAT ( [Date], "MMMM" ) but this doesn't seem to help maintaining a coninuous drill-down.
This image illustrates the difference. The problem occurs on the date hierarchy I've created myself (left) where I can't drill down and keep the X-axis as Continous. But it works on the hierarchy Power BI has created automatically using the date-column (right).
Anonymous and pbit,
I asked a similar question in a comment thread on SQLBI. Link here: http://disq.us/p/28db3dz
Marco Russo responded with an answer, and it sounds like they're publishing an article about it soon. I'll paste his comment and example below. I hope this helps!
"Power BI uses an internal attribute (data category) that cannot be modified in Power BI.
You can obtain the same behavior by creating columns as dates and formatting them as you want. We will publish an article about this in a few weeks, the principle is using a calculated table like the one below and then set the custom format of the columns using strings as yyyy (year) and mmm yyyy (month year)..."Date =
VAR FirstFiscalMonth = 7 -- First month of fiscal year
VAR FirstDayOfWeek = 0 -- 0 = Sunday, 1 = Monday, ...
VAR FirstYear = -- Customize first year to use
YEAR ( MIN ( Sales[Order Date] ))
RETURN
GENERATE (
FILTER (
CALENDARAUTO (),
YEAR ( [Date] ) >= FirstYear
),
VAR Yr = YEAR ( [Date] ) -- Year Number
VAR Mn = MONTH ( [Date] ) -- Month Number (1-12)
VAR Qr = QUARTER ( [Date] ) + 1 -- Quarter Number (1-4)
VAR MnQ = Mn - 3 * (Qr - 1) -- Month in Quarter (1-3)
VAR Wd = WEEKDAY ( [Date], 1 ) - 1 -- Week day number (0 = Sunday, 1 = Monday, ...)
VAR Fyr = -- Fiscal Year Number
YEAR ( DATE ( Yr, Mn + FirstFiscalMonth - 1, 1 ) )
VAR Fqr = -- Fiscal Quarter (string)
FORMAT ( EOMONTH ( [Date], 1 - FirstFiscalMonth ), "\QQ" )
RETURN ROW (
"Year", DATE ( Yr, 12, 31 ),
"Year Quarter", FORMAT ( [Date], "\QQ-YYYY" ),
"Year Quarter Date", EOMONTH ( [Date], 3 - MnQ ),
"Quarter", FORMAT ( [Date], "\QQ" ),
"Year Month", EOMONTH ( [Date], 0 ),
"Month", DATE ( 1900, MONTH ( [Date] ), 1 ),
"Day of Week", DATE ( 1900, 1, 7 + Wd + (7 * (Wd < FirstDayOfWeek)) ),
"Fiscal Year", DATE ( Fyr, FirstFiscalMonth, 1 ) - 1,
"Fiscal Year Quarter", "F" & Fqr & "-" & Fyr,
"Fiscal Year Quarter Date", EOMONTH ( [Date], 3 - MnQ ),
"Fiscal Quarter", "F" & Fqr
)
)
9 Replies
Anonymous and pbit,
I asked a similar question in a comment thread on SQLBI. Link here: http://disq.us/p/28db3dz
Marco Russo responded with an answer, and it sounds like they're publishing an article about it soon. I'll paste his comment and example below. I hope this helps!
"Power BI uses an internal attribute (data category) that cannot be modified in Power BI.
You can obtain the same behavior by creating columns as dates and formatting them as you want. We will publish an article about this in a few weeks, the principle is using a calculated table like the one below and then set the custom format of the columns using strings as yyyy (year) and mmm yyyy (month year)..."Date =
VAR FirstFiscalMonth = 7 -- First month of fiscal year
VAR FirstDayOfWeek = 0 -- 0 = Sunday, 1 = Monday, ...
VAR FirstYear = -- Customize first year to use
YEAR ( MIN ( Sales[Order Date] ))
RETURN
GENERATE (
FILTER (
CALENDARAUTO (),
YEAR ( [Date] ) >= FirstYear
),
VAR Yr = YEAR ( [Date] ) -- Year Number
VAR Mn = MONTH ( [Date] ) -- Month Number (1-12)
VAR Qr = QUARTER ( [Date] ) + 1 -- Quarter Number (1-4)
VAR MnQ = Mn - 3 * (Qr - 1) -- Month in Quarter (1-3)
VAR Wd = WEEKDAY ( [Date], 1 ) - 1 -- Week day number (0 = Sunday, 1 = Monday, ...)
VAR Fyr = -- Fiscal Year Number
YEAR ( DATE ( Yr, Mn + FirstFiscalMonth - 1, 1 ) )
VAR Fqr = -- Fiscal Quarter (string)
FORMAT ( EOMONTH ( [Date], 1 - FirstFiscalMonth ), "\QQ" )
RETURN ROW (
"Year", DATE ( Yr, 12, 31 ),
"Year Quarter", FORMAT ( [Date], "\QQ-YYYY" ),
"Year Quarter Date", EOMONTH ( [Date], 3 - MnQ ),
"Quarter", FORMAT ( [Date], "\QQ" ),
"Year Month", EOMONTH ( [Date], 0 ),
"Month", DATE ( 1900, MONTH ( [Date] ), 1 ),
"Day of Week", DATE ( 1900, 1, 7 + Wd + (7 * (Wd < FirstDayOfWeek)) ),
"Fiscal Year", DATE ( Fyr, FirstFiscalMonth, 1 ) - 1,
"Fiscal Year Quarter", "F" & Fqr & "-" & Fyr,
"Fiscal Year Quarter Date", EOMONTH ( [Date], 3 - MnQ ),
"Fiscal Quarter", "F" & Fqr
)
)I still can't get it working. Can you show me your date hierarchy based on these fields? What am I missing?
My date hierarchy created from custom date table:
My Year field:
My Month field:
Hi! krconrad was right. The SQBI Team published an article on this issue.
Here it is: https://www.sqlbi.com/articles/improving-temporal-line-charts-in-power-bi-with-dax/
As always, it is interesting.
Enjoy.
G.
v-piga-msftResident Rockstar
Hi Anonymous,
By my test with creating a date hierarchy, it could be continuous and the Forecasting will work like below.
You should check if the date you created is a Date type or a text type. The feature Forecast is available for line charts only and the x-axis value needs to have a date/time format or be a uniformly increasing whole number.
In addition, you could have a reference of this article about forcasting.
If you need additional help, please share your data sample so that we could help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)
Best Regards,
Cherry
Was a solution ever found for this?
Was a solution ever found for this issue? I'm having the exact same problem.
Still no solution to this problem? I’m having the same issue.
