13. DAX: Data Analysis Expressions
13.15 Date Functions
Today = TODAY() -- current date
Now Time = NOW() -- current date and time
Order Year = YEAR(Orders[Order Date])
Order Month = MONTH(Orders[Order Date])
Order Hour (DAX) = HOUR(Orders[Order DateTime]) -- 0 to 23
-- Calculated column: delivery time in minutes from the two timestamps
Delivery Mins (calc) =
IF(
NOT ISBLANK(Orders[Delivered DateTime]),
DATEDIFF(Orders[Order DateTime], Orders[Delivered DateTime], MINUTE)
)
-- Customer tenure in months (calculated column in Customer)
Tenure Months = DATEDIFF(Customer[Signup Date], TODAY(), MONTH)
Month End = EOMONTH(Orders[Order Date], 0) -- last day of the same month
Start of Year = DATE(YEAR(Orders[Order Date]), 1, 1)
Week Number = WEEKNUM('Date'[Date], 2) -- weeks starting Monday
Weekday No = WEEKDAY('Date'[Date], 2) -- Monday = 1 … Sunday = 7
Other useful date functions: EDATE (add months), STARTOFMONTH, ENDOFMONTH, STARTOFYEAR, ENDOFYEAR, CALENDAR, CALENDARAUTO.
Ravindra Bagale's Tip
A common mistake is using TODAY() in measures and then being confused when the report shows different numbers each day, or when data refreshes later than expected. Base "current period" logic on the latest date with data, for example MAX(Orders[Order Date]), when that is what the business means. Don't make this mistake!
Ravindra Bagale's Tip – मराठी
एक common चूक म्हणजे measures मध्ये TODAY() वापरणं आणि मग report रोज वेगळे numbers दाखवतो किंवा data अपेक्षेपेक्षा उशिरा refresh होतो तेव्हा गोंधळणं. Business ला तसंच अपेक्षित असेल तर "current period" चं logic data असलेल्या शेवटच्या date वर ठेवा, उदाहरणार्थ MAX(Orders[Order Date]). ही चूक करू नका!
Ravindra Bagale's Tip – हिंदी
एक common गलती है measures में TODAY() इस्तेमाल करना और फिर उलझ जाना जब report हर दिन अलग numbers दिखाए, या data उम्मीद से देर से refresh हो. जब business का मतलब यही हो तो "current period" का logic data वाली आख़िरी date पर रखो, जैसे MAX(Orders[Order Date]). यह गलती मत करना!