using YTD and privious YTD up to the last load date for calendar that starts in July 1st.

[BIXL]

Hi All,

My Fisical year starts from July 1st untill Jun 30ty of the next year.

for exmaple the date 01-APR-2016 in in year 2016 but in fisical year 2015 (wich starts from 01-July-2015 and ends at 30-Jun-2016.

I am trying to have the YTD and previous YTD to work but, I need it to calculate up to the last load date  for current YTD and up to the last load date minus 1 year, for the YTD of the privious year.

since my date table has values later then the current date (aka sysdate), when I apply the SAMEPIRIODLASTYEAR to the YTD of the current year, I get values later then the current sysdate minus 1 year.

my measures :

TotalYTD_Fisical =
VAR CurrentDate=NOW()
RETURN
IF(HASONEVALUE(DimDates[FisicalYear])=TRUE(),
CALCULATE([TotalSales],
FILTER(DATESYTD(DimDates[DateKey],"30/06")
,DimDates[DateKey]<=CurrentDate
)
)
)

TotalYTD_Fisical-PrevYear =
IF(HASONEVALUE(DimDates[FisicalYear])=TRUE(),
CALCULATE([TotalYTD_Fisical],SAMEPERIODLASTYEAR(DimDates[DateKey]))
)

I would have expected to see from the start of the red rectangle till the end the same value as from 201611 prev year : 148,521,637

what am I misssing ?

thanks

[v-haibl-msft]

@BIXL

If the DimDates table has relationship with Fact table with DateKey here. You can create two columns of YearMonth and FisicalYear in your fact table (Table1) with following formulas.

YearMonth = 
YEAR ( Table1[Date] ) * 100
+ MONTH ( Table1[Date] )

FisicalYear = 
RELATED ( DimDates[FisicalYear] )

Then drag these two column instead of columns in DimDates into Table chart as below.

In my test Table1, 11/2/2016 is the last load date, 5823 is the total YTD until 11/2/2015. (6731 is the total YTD until 11/30/2015)

Best Regards,

Herbert