Hi there:
If you have a separate Date Table which is marked as such and is connected to your fact table the folllowing Dax Measure will work. You can substitute your table names instead of mine. Here I Am summing up a column in my Sales "Fact" table and using time intelligence functions to get results. Time Intel only works correctly with separate date table. I will also paste Date Table code for you.
MODELING>New Table> then code below. The table names is "Dates"
Dates =
-- Specify a start date and end date
VAR StartDate = Date(2021,1,1)
VAR EndDate = Today() + 243
VAR FiscalMonthEnd = 12
-- Generate a base table of dates
VAR BaseTable = Calendar(StartDate, EndDate)
-- Add the Year for each individual date
VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))
-- Add the calendar month and other month related data for each date
VAR Months = ADDCOLUMNS(
Years,
"Month",MONTH([Date]),
"Year and Month Number",FORMAT([Date],"YYYY-MM"),
"Year and Month Name",FORMAT([Date],"YYYY-MMM"),
"Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),
"Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),
IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))
)
-- Add the Quarter and other quarter related data for each date
VAR Quarters = ADDCOLUMNS(
Months,
"Quarter",ROUNDUP(MONTH([Date])/3,0),
"Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))
-- Add the Day and other day related data for each date
VAR Days = ADDCOLUMNS(
Quarters,
"Day",DAY([Date]),
"Day Name",FORMAT([Date],"DDDD"),
"Day Of Week",WEEKDAY([Date]),
"Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)
-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date
VAR Weeks = ADDCOLUMNS(
Days,
"Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,
"Week of Year (Sunday)",WEEKNUM([Date],1),
"Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))
-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.
var WorkingDays = ADDCOLUMNS(
Weeks,
"Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})
RETURN WorkingDays
I hope this gives you a good solution. I will also paste a link to a file with this same calculation.
https://drive.google.com/file/d/11TnA0izWB65WgnsuJeDiGeuYrUN60gOG/view?usp=sharing
Rollling 12 Month Sales =
Calculate(Sum(Sales[SalesAmt]),
Datesbetween
(Dates[Date],
NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE(Dates[Date]))),
LASTDATE(Dates[Date]),
ALL(Dates))
