Forum Discussion
How to remove Date Hierarchy
- Anonymous7 years ago
Sorry wrong advise.... actually when you drag it into values, you can right click and select the date name instead of the Date Hierarchy - then it will display as a single date.
If you turn the auto date stuff off.. you would then have to have your own date tables.
Perfect! Thank you Ashish_Mathur for your response.
Just to summarize for anyone who may come across this same issue...
My goal was to calculate sales per week. I was able to calculate sales per week using the following formula:
WeeklySales = SUMX('orderDetails',[quantitySold] * [priceEach] ) // row-by-row sales
This line of code generates row-by-row sales however it does not sum the sales for each week. In order to sum sales for each week, I need to define a week. This is where the WEEKNUM() function is used.
The challenges that I had were that 1) My data was imported as a hierarchy and 2) I was using WEEKNUM() within a measure therefore the date hierarchy was passed as an argument. Using Ashish_Mathur suggestion to create a calculated column the entire date (not hierarchy) is passed as an argument.
At a minimum create a calculated column like this:
OrderWeekNo = WEEKNUM([orderDate]) // add this as calculated column, NOT MEASURE
// Select the column and "Don't Summarize" in the
// summarize option otherwise it returns 1 number.
// DO NOT convert this new column to a date. Use Integer
However if you use this simple formula then weeks are aggregated for each year. For example week 1 sum would be the sum of each week 1 from 2003 + 2004 + 2005.... This can be helpful if you want to understand the sum of performance for week 1 for each year. It's not what I wanted because I wanted to see a nice visual line chart with performance for each week.
Using the measure and column code I was able to modify in the visual itself by adding the year from the hierarchy ([orderDate].[year]), OrderWeekNo, and then product of the quantityOrdered and priceEach columns to the visual. This allowed me to separate the calculated $$ by the week number and keep each year separate without converting dates to strings.
Read this post.
Now I have one additional question Ashish_Mathur because I like to dig deeper into challenges like this. My question is WHY? Why does the weekly date need to be a column rather than a measure?
In my opinion this is duplicating an existing column from the original data. The DAX documentation for WEEKNUM() does state that it applies to a Measure. WEEKNUM()
If you have a moment, kindly help me to understand why I would need a column? What is the tradeoff of adding an additional column?
- Ashish_Mathur2 years agoSuper User
You are welcome. Measures are generally used when you want dynamic results based on slicer/filter selections. Weeknum does not have to be a dynamic solution. No matter what is selected in the slicer/filter, the weeknum of that date will remain the same.
- MSFTTrainUser2 years agoFrequent Visitor
Thank you again Ashish_Mathur for your time and attention!