SAMEPERIOD 2 YEARS BACK H2 AND LASTYEAR H1 QUANTITY CALCULATION

Gopimadhaiyan — Mon, 09 Nov 2020 12:32:46 GMT

Hi Team,

Pls help me to find out the DAX calcuation to calculate the

FISCAL YEAR 2018 - H2

FISCAL YEAR 2019 - H1

in sequence to see the whole 12 months calculation in FISCAL YEAR 2020

Request you to help me pls

Re: SAMEPERIOD 2 YEARS BACK H2 AND LASTYEAR H1 QUANTITY CALCULATION

amitchandak — Mon, 09 Nov 2020 13:04:46 GMT

Gopimadhaiyan , Not very clear. You need to have the following column in the date table

Start Year = STARTOFYEAR('Date'[Date],"12/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)

And measures like
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))

2019 ist is 3 or 4 half year back

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

Also a measure like this should also help

Half Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-6,Month))

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.

Re: SAMEPERIOD 2 YEARS BACK H2 AND LASTYEAR H1 QUANTITY CALCULATION

Gopimadhaiyan — Tue, 10 Nov 2020 11:42:06 GMT

Thanks sir.

I am still getting error for Half Year Rank = RANKX(all('Date'),'Date'[Half Year Start],,ASC,Dense)

pls check and suggest.

My ask is

Fiscal year = FY19 (SEP-AUG), FY20(SEP-AUG)

I would like to calculate the total quantity for FY21 (H2FY19+H1FY20)

topic Re: SAMEPERIOD 2 YEARS BACK H2 AND LASTYEAR H1 QUANTITY CALCULATION in DAX Commands and Tips

SAMEPERIOD 2 YEARS BACK H2 AND LASTYEAR H1 QUANTITY CALCULATION

Re: SAMEPERIOD 2 YEARS BACK H2 AND LASTYEAR H1 QUANTITY CALCULATION

Re: SAMEPERIOD 2 YEARS BACK H2 AND LASTYEAR H1 QUANTITY CALCULATION