Defining Previous Fiscal Year - Var

[AndySmith]

Hi - I have been using the below DAX from the brilliant Ried Havens to slice data by time periods such as YTD, MTD, PYTD etc, 

What I need is to define the start of Previous Fiscal Year with a start date of 01/07/2020 in order to calculate the PYTD.

The important part is below in bold but full code included.

VAR PYYearStart = CALCULATE(DATEADD(STARTOFYEAR(FactTable[Date.Date]), -1, YEAR), YEAR(FactTable[Date.Date]) - 1 = YEAR(PYTodayDate) )

Time Intelligence Selection =

//Current Year Variables

VAR TodayDate = today()

VAR MonthStart = CALCULATE(STARTOFMONTH(FactTable[Date.Date]), YEAR(FactTable[Date.Date]) = YEAR(TodayDate), MONTH(FactTable[Date.Date]) = MONTH(TodayDate) )

VAR QuarterStart = CALCULATE(STARTOFQUARTER(FactTable[Date.Date]), YEAR(FactTable[Date.Date]) = YEAR(TodayDate), QUARTER(FactTable[Date.Date]) = QUARTER(TodayDate) )

VAR YearStart = CALCULATE(STARTOFYEAR(FactTable[Date.Date]), YEAR(FactTable[Date.Date]) = YEAR(TodayDate) )

VAR FiscalYearStart =

IF( MONTH(TodayDate) <= 6,

DATE(YEAR(TodayDate) -1 , 7,1),

DATE(YEAR(TodayDate), 7, 1)

)

VAR FiscalYearEnd =

IF( MONTH(TodayDate) <= 6,

DATE(YEAR(TodayDate), 6, 30),

DATE(YEAR(TodayDate) + 1, 6, 30)

)

//Previous Year Variables

VAR PYTodayDate = DATE(YEAR(TodayDate) - 1, MONTH(TodayDate), DAY(TodayDate) )

VAR PYMonthStart = CALCULATE(DATEADD(STARTOFMONTH(FactTable[Date.Date]), -1, YEAR), YEAR(FactTable[Date.Date]) - 1 = YEAR(PYTodayDate), MONTH(FactTable[Date.Date]) = MONTH(TodayDate) )

VAR PYQuarterStart = CALCULATE(DATEADD(STARTOFQUARTER(FactTable[Date.Date]), -1, YEAR), YEAR(FactTable[Date.Date]) - 1 = YEAR(PYTodayDate), QUARTER(FactTable[Date.Date]) = QUARTER(TodayDate) )

VAR PYYearStart = CALCULATE(DATEADD(STARTOFYEAR(FactTable[Date.Date]), -1, YEAR), YEAR(FactTable[Date.Date]) - 1 = YEAR(PYTodayDate) )

VAR Result =

UNION (

//All Dates Filter For Single Selection Slicers

ADDCOLUMNS (

CALENDAR( MIN(DateTable[Date]), MAX(DateTable[Date]) ),

"selection", "ALL",

"Sort", 1

),

//Current Year Date Ranges

ADDCOLUMNS (

CALENDAR ( MonthStart, TodayDate ),

"selection", "MTD",

"Sort", 2

),

ADDCOLUMNS (

CALENDAR ( QuarterStart, TodayDate ),

"selection", "QTD",

"Sort", 3

),

ADDCOLUMNS (

CALENDAR ( YearStart, TodayDate ),

"selection", "YTD",

"Sort", 4

),

//Previous Year Date Ranges

ADDCOLUMNS (

CALENDAR ( PYMonthStart, PYTodayDate ),

"selection", "PMTD",

"Sort", 5

),

ADDCOLUMNS (

CALENDAR ( PYQuarterStart, PYTodayDate ),

"selection", "PQTD",

"Sort", 6

),

ADDCOLUMNS (

CALENDAR ( PYYearStart, PYTodayDate ),

"selection", "PYTD",

"Sort", 7

),

ADDCOLUMNS (

CALENDAR ( FiscalYearStart, FiscalYearEnd ),

"Selection", "FYTD",

"Sort", 8

)

)

RETURN

Result

[amitchandak · Accepted solution]

@AndySmith , In case you are trying to calendar take it from

.Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

new columns

Last year start date  =

Startofyear(dateadd([Date],-1,year),"6/30")

Last year End date  =

Endofyear(dateadd([Date],-1,year),"6/30")

make sure you calendar also start from July