Calculate two different fiscal year

[Gopimadhaiyan]

Hi All,

Pls help me to get the right measure for calculating the quantities for below 2 fiscal year 

FY19 - (SEP'18-AUG'19)

FY20 - (SEP'19-AUG'20)

FY21 = TOTAL QUANTITY CALCUALTION (H2 FY19 + H1 FY20)

[amitchandak]

@Gopimadhaiyan , do you half-year or year. In both cases. use the date table and have FY based on that. Refer to my blog to get you FY- It has 12 calendars  -https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

Add these columns for half year

Start Year = STARTOFYEAR('Date'[Date],"8/31")
Half = Half = if(datediff([start year],[Date],month)<6,1,2)
Half Year = [Start Year]*100 + [Half]
Half Year Rank = RANKX(all('Date'),'Date'[Half Year Start],,ASC,Dense)

measures for half year
This Half Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Half Year Rank]=max('Date'[Half Year Rank])))
Last Half Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Half Year Rank]=max('Date'[Half Year Rank])-1))
3rd Last Half Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Half Year Rank]=max('Date'[Half Year Rank])-3))

4th Last Half Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Half Year Rank]=max('Date'[Half Year Rank])-4))

5th Last Half Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Half Year Rank]=max('Date'[Half Year Rank])-5))

For year

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"8/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"8/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"8/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"8/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"8/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.