cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BIXL Resolver I
Resolver I

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

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

 

YTD.jpg

what am I misssing ?

 

thanks

 

1 REPLY 1
Microsoft
Microsoft

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

@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] )

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

 

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)

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

 

Best Regards,

Herbert

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors