Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I need to calculate YTD for financial year
Current year--- 2022 Apr to 2023 mar(CY Value from 2022 Apr to till date)
pervious year --2021 apr to 2022 mar(PY value from 2021 Apr ro 2022 mar)
and i need show like below in ssas cube
Can you help to write dax(without selection date filter we need dax calaculation)
Solved! Go to Solution.
Hi:
I hope you have Date Table, marked as Date tble and with one to many relationship with fact table. I will paste some code where you should be able to name annual beginning of year.
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"31-03"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"9/30"))
You can substitute your measure where I have bolded above.
Measure Total Sales = SUM(Sales[Sales Amt])
Date Table named dates:
Dates =
-- Specify a start date and end date
VAR StartDate = Date(2021,4,1)
VAR EndDate = Today()
VAR FiscalMonthEnd = 3
-- 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
Hi:
I hope you have Date Table, marked as Date tble and with one to many relationship with fact table. I will paste some code where you should be able to name annual beginning of year.
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"31-03"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"9/30"))
You can substitute your measure where I have bolded above.
Measure Total Sales = SUM(Sales[Sales Amt])
Date Table named dates:
Dates =
-- Specify a start date and end date
VAR StartDate = Date(2021,4,1)
VAR EndDate = Today()
VAR FiscalMonthEnd = 3
-- 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