Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a DAX query to look at last years sales using Adventureworks
Last year and Sales are now the same. Im trying to get my hear round why adding the .Date changes the Query so much. Has anyone got an easy to understand answer to this one?
Thnaks in advence
Solved! Go to Solution.
hi @DebbieE
Yes, usually, we don't need to use .[Date] in dax, it only work in date hierarchy row context.
https://docs.microsoft.com/en-us/power-bi/desktop-auto-date-time
Regards,
Lin
hi @DebbieE
.[Date] only calculate in date hierarchy of one viusal.
here is a simple sample will give you a better understand.
Last Years Internet Sales = CALCULATE(SUM('Table'[Qty]), DATEADD('Date'[Date],-12,MONTH))
Last Years Internet Sales 2 = CALCULATE(SUM('Table'[Qty]), DATEADD('Date'[Date].[Date],-12,MONTH))
Regards,
Lin
This concept is just so hard to understand. You say that .date means that it only works on the date rather than the hierarchy but my visual does use date rather than the hierarchy so I still just dont understand how this all works.
hi @DebbieE
".[Date]" only works on hierarchy date rather than date, this just means when you use Date hierarchy for your date filed, it will work.
Regards,
Lin
Ahhhh so If Im using the date hierarchy then .Date should be fine. However because Im using just date I dont need the .date. Ill have to test that out. Thankyou.
hi @DebbieE
Yes, usually, we don't need to use .[Date] in dax, it only work in date hierarchy row context.
https://docs.microsoft.com/en-us/power-bi/desktop-auto-date-time
Regards,
Lin
Quite Interesting..
I Just have a theory..
If I have a table with one row and one date column with value 1/1/2019, and when I create a table visual with date, I only see 1 row - 1/1/2019.
But when I change the attribute date to show hierarchy, I see all the dates in 2019.
Also, test this out by creating two new tables as below.
New Table = DATEADD(TABLE1[DATE].[DATE],-11,MONTH)
New Table = DATEADD(TABLE1[DATE],-11,MONTH)
The first table(With hierarchy) returns only January dates(-11 actually removes all the preceding 11 months on 12/31/2019; when you specify -12, table is null)
The second table(without hierarchy) actually brings in previous year if you have data in table, otherwise null.
Basically the DATEADD function behaves differently when we specify hierarchy.
So, looking at your example, when you use hierarchy, the dateadd returns null and it has no effect on CALCULATE statement.
Disable the date hierarchy in Desktop - Options->data load ->Time intelligence->Auto Date/Time
-vasanth
I will have a work through this. Just having a look at this answers Im just as stuck as I was before. Sometimes Dax is incredibly difficult to get your her around
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |