wtd

5 Topics

TOTALWTD not recognizing Calendar
Trying to use the new TOTALWTD DAX function with AdventureWorks sample dataset: TOTALWTD( SUM(Sales[Sales Amount]), CalendarTable[Date]) ) but it's giving me an error: TOTALWTD's parameter 2 must be a calendar reference. My Calendar table is completely fine: no gaps, marked as a Calendar table. If I change TOTALWTD to TOTALYTD, so that would be TOTALYTD( SUM(Sales[Sales Amount]), CalendarTable[Date]) ) I'm getting no errors; suddenly the Calendar table is fine. Is anyone else able to use this function? What am I missing?
Solved
linkide
10 months ago Place DAX Commands and Tips
1.3KViews
0likes
4Comments
Adding WTD date range to special date slicer
So I have this DAX Expression for my customized Date range slicer. I want to add a WTD date range as well. NewSpecialDates = VAR _datetable = NewDateRange VAR _today = TODAY() VAR _month = MONTH(TODAY()) VAR _year = YEAR(TODAY()) VAR _thismonthstart = DATE(_year,_month,1) VAR _thisyearstart = DATE(_year,1,1) VAR _lastmonthstart = EDATE(_thismonthstart,-1) VAR _lastmonthend = _thismonthstart-1 VAR _thisquarterstart = DATE(YEAR(_today),SWITCH(true,_month>9,10,_month>6,7,_month>3,4,1),1) RETURN UNION( ADDCOLUMNS(FILTER(_datetable,[Date]=_today),"Period","Today","Order",1), ADDCOLUMNS(FILTER(_datetable,[Date]=_today-1),"Period","Yesterday","Order",2), ADDCOLUMNS(FILTER(_datetable,[Date]>=_today-7),"Period","Last 7 Days","Order",3), ADDCOLUMNS(FILTER(_datetable,[Date]>=_today-14),"Period","Last 2 Weeks","Order",4), ADDCOLUMNS(FILTER(_datetable,[Date]>=_thismonthstart && [Date]<=TODAY()),"Period","This Month","Order",6), ADDCOLUMNS(FILTER(_datetable,[Date]>=_thisyearstart),"Period","This Year","Order",7), ADDCOLUMNS(FILTER(_datetable,[Date]>_today-30),"Period","Last 30 Days","Order",8), ADDCOLUMNS(_datetable,"Period","Custom Range","Order",9) ) This is my DAX for WTD: Start of Week = var CurrentDate=LASTDATE('NewDateRange'[Date]) var DayNumberOfWeek=WEEKDAY(LASTDATE('NewDateRange'[Date]),3) return DATEADD( CurrentDate, -1*DayNumberOfWeek, DAY) How would I add this to the above Special Dates DAX so when I select "This Week" data range it will show all data from the start of the week to TODAY()?
Solved
Anonymous
3 years ago Place DAX Commands and Tips
664Views
1like
2Comments
WTD, MTD, QTD and YTD calculation on aggregated data
Hi 🙂 I am currently working with some data, which is aggregated in that sense, that it is a snapshot that is taken each day which is the total. I.e. I have the following table which does only include business days - I am using a datetable for that named DimDate Date Group Market Value 08/03/2023 Net Asset Value 100 09/03/2023 Net Asset Value 102 10/03/2023 Net Asset Value 103 13/03/2023 Net Asset Value 101 I have tried using the built-in functionalities like TotalYTD etc. but they sums it all up providing me with a very large number. Based on the above table I would like the YTD to be 101 - 100 = 1 Does anyone know how to solve this? Please let me know if anything else is required from my side to sort this. Best Regards, Solle
Solved
Solle
3 years ago Place DAX Commands and Tips
1.7KViews
0likes
2Comments
WTD, MTD, YTD working hours of each employee with different timestamps for each day using DAX
This is a sample data. I need to calculate working hours in DAX. Working hours for one day = Last Timestamp of the day - First Timestamp of the day It is easy to calculate for one day but I can't seem to figure out how to calculate total working hours WTD, MTD, YTD. Every day timestamps are different and we want working hours for each Worker(Name) separately. Please guide. I can share the actual data as well if anybody requires. amitchandak Anonymous Jihwan_Kim daxer-almighty PaulOlding Fowmy
Solved
manuvats
5 years ago Place DAX Commands and Tips
2.4KViews
0likes
7Comments
Dax formula for "4 Weeks to Date" (I have 1WTD)
Hi there. In the first image below I have plotted "One Week to Date (1WTD)" sales and "Month to Date (MTD)" sales. I would like to replace MTD with "4 weeks to date (4WTD)". I would then get a consistent pattern as illustrated in image 2 (where I hand drew 4WTD). Question: Could someone please help me with the DAX code for "4WTD" similar to my 1WTD formula? Dataset & .pbix: here (dataset from Enterprise DNA's Ultimate Beginners Guide to Dax) 1WTD formula: (formula from RADCAD's tutorial ) As always, thank you. Michelle
Solved
michellepace
6 years ago Place DAX Commands and Tips
4.1KViews
0likes
6Comments