Navigating Specialized Date Computations in Power BI & Tableau.

In the dynamic field of data analytics, mastering the subtleties of date calculations is a crucial skill that can enhance the insights derived from your visualizations. As organizations pursue increasingly advanced analytics solutions, the capacity to perform specialized date computations emerges as a strategic advantage. This article takes you on a journey through the complexities of specialized date computations, uncovering how Power BI and Tableau, two prominent data visualization tools, can be employed to unleash the complete potential of temporal analysis. Join us as we delve into the applications, challenges, and best practices, discovering how to leverage these robust platforms for nuanced insights into date-related data.

Power BI - Date Calculations

1. End of Month = 'Sample - Superstore'[Order Date] & " - " & EOMONTH(DATE(YEAR('Sample - Superstore'[Order Date]),MONTH('Sample - Superstore'[Order Date])-1,DAY('Sample - Superstore'[Order Date])),1)

2. Last 12 Months = VAR Currentdate = MAX('Monthly Table'[YearMonth])
 VAR PREVIOUSDATE = DATE(YEAR(Currentdate),MONTH(Currentdate)-11,DAY(Currentdate))
 VAR Result = CALCULATE(SUM('Sample - Superstore'[Sales]),FILTER('Sample - Superstore','Sample - Superstore'[Order Date] >= PREVIOUSDATE &&
 'Sample - Superstore'[Order Date] <= Currentdate))
 RETURN
 Result

3. Prior 12 Months = VAR CurrentDate = MAX('Monthly Table'[YearMonth])
VAR PreviousDate_12 = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate))
VAR PreviousDate_23 = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-23,DAY(CurrentDate))
VAR RESULT = CALCULATE(SUM('Sample - Superstore'[Sales]),FILTER('Sample - Superstore','Sample - Superstore'[Order Date] >= PreviousDate_23 && 'Sample - Superstore'[Order Date] <= PreviousDate_12))
RETURN
RESULT

4. Prior YTD = VAR CurrentDate = MAX('Monthly Table'[YearMonth])
RETURN
CALCULATE(SUM('Sample - Superstore'[Sales]),
YEAR('Sample - Superstore'[Order Date])=YEAR(CurrentDate)-1,
MONTH('Sample - Superstore'[Order Date])<=MONTH(CurrentDate),
FILTER('Sample - Superstore','Sample - Superstore'[Order Date] <= CurrentDate))

5. Same Month Dimensions = VAR CURRENTDATE = MAX('Monthly Table'[YearMonth])
VAR Previous_Date = DATE(YEAR(CURRENTDATE),MONTH(CURRENTDATE)-12,DAY(CURRENTDATE))
VAR RESULT = SWITCH(TRUE(),SELECTEDVALUE('Sample - Superstore'[Order Date]) >= Previous_Date && SELECTEDVALUE('Sample - Superstore'[Order Date]) <= Previous_Date,Previous_Date,
SELECTEDVALUE('Sample - Superstore'[Order Date]) >= CURRENTDATE && SELECTEDVALUE('Sample - Superstore'[Order Date]) <= CURRENTDATE,CURRENTDATE)
RETURN
RESULT

6. Previous Year-Month Values = VAR CurrentDate = MAX('Monthly Table'[YearMonth])
VAR Previous_Year_Month = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate))
VAR RESULT = CALCULATE(SUM('Sample - Superstore'[Sales]),FILTER('Sample - Superstore','Sample - Superstore'[Order Date] >= Previous_Year_Month && 'Sample - Superstore'[Order Date] <= Previous_Year_Month))
RETURN
RESULT

Tableau - Date Calculations

1. End of Month
STR(DATE(MIN([Month Year]))) + ' - ' +
STR(DATE(DATEADD('day',-1,DATEADD('month',1,DATETRUNC('month',Max([Month Year]))))))

2. Last 12 Months

IF DATEDIFF('month', [Month Year], [Report Date]) <=11
AND DATEDIFF('month', [Month Year], [Report Date]) >=0
THEN [Sales] END

3. Prior Last 12 Months

IF DATEDIFF('month',[Month Year], [Report Date]) <=23
AND DATEDIFF('month', [Month Year], [Report Date]) >=12
THEN [Sales]
END

4. Prior YTD

IF DATEDIFF('year', [Month Year], [Report Date]) =1
AND MONTH([Month Year]) <= Month([Report Date])
THEN [Sales]
END

5. Current Month
IF DATEDIFF('month', [Month Year], [Report Date]) =0
THEN [Sales]
END

6. Same Month Dimensions

IF DATEDIFF('year', [Month Year], [Report Date]) =0
AND MONTH([Month Year]) = Month([Report Date]) then [Month Year]
ELSEIF DATEDIFF('year', [Month Year], [Report Date]) =1
AND MONTH([Month Year]) = Month([Report Date]) then [Month Year]
END

7. Same Month Prior Year

IF DATEDIFF('month',[Month Year], [Report Date]) =12
THEN [Sales]
END

Please feel free and provide your valuable feedbacks.