Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have a problem with format of my Date field. The field is recognized as date field, it shows values chronologically, but I can not use the date field as hierarchies. The date value is showed in format Month.Day.Year 12:00:00. For example June 30th 2022 would be shown 06.30.22 12:00:00. I can not format the field because I import data via Power BI dataset and this option is not available. Anozher issue is that I can not create any measure where with date functions (for example Year-to-Year comparison or rolling average) where date hierarchies must be used. Anyone can help with this? Thanks a lot.
Solved! Go to Solution.
Hi @dmatliak ,
The date hierarchy will work for you when the below conditions are met:
For more details, please kindly refer to the blog:
Why there is no date hierarchy - Microsoft Power BI Community
When you connect to Power BI datasets, the default mode is Live connection, which prohibits changing the data type/data format/ create custom hierarchy.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @dmatliak ,
The date hierarchy will work for you when the below conditions are met:
For more details, please kindly refer to the blog:
Why there is no date hierarchy - Microsoft Power BI Community
When you connect to Power BI datasets, the default mode is Live connection, which prohibits changing the data type/data format/ create custom hierarchy.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@dmatliak , if you have date table, joined with your date. You should use time intellignece. Using date hierarchy is not a good idea
Example measures
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
Hi, thanks for you quick reply. But there is a problem with formate of my date field ("leave_date"). It is not formatted properly as date. And I can not format is because I import data from Power BI dataset. See below, I wanted to calculate YTD number of lost clients, but it did not work properly because "leave_date" is not a date field.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
85 | |
82 | |
66 | |
49 |
User | Count |
---|---|
137 | |
111 | |
101 | |
66 | |
65 |