Next 12 and 24 Months based on selected value

[joshua1990]

Hello everyone!

I would like to sum up the next 12 and 24months based on the selected value.

I have an order table that shows me the orders for the next 36 months. This order table is archived weekly.

For every archived week, I have the next 36 months.

The structure is like this:

Archive-Week-Year	Value	Week-Year
01-2021	50	01-2021
01-2021	50	02-2021
02-2021	40	01-2021

Our fiscal calendar is linked to the "Week-Year" column so that we can aggregate this data into months, quarters etc.

The selected value is the column "Archive-Week-Year".

If I selected archive week "01-2021", then I would like to get the next 12 fiscal months including the months of the selected value.

How would you do that?

[amitchandak]

@joshua1990 , with a date table

examples

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),12,MONTH))

Rolling 24 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),24,MONTH))

Rolling 12 till next12 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],maxX('Date', dateadd('Date'[Date],12,month)),12,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.

[joshua1990]

@amitchandak Thank you! But are you sure, that this will work with a fiscal calendar with specific start and end dates for each fiscal month?

[amitchandak]

@joshua1990 , this rolling 12, should work with standard months , if you do have standard months if should.

FY with year

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

[joshua1990]

Ok, then it is not what I am looking for since we work with a fiscal calendar, as I said in the first post. 

[amitchandak]

@joshua1990 , FY with the standard month (Jan , feb match) it should work. If Month are 445 etc , 28*13

Create a Rank in you Calendar table on month or Week

Period Rank = RANKX(all('Period'),'Period'[year period],,ASC,Dense)  // format should be YYYYPP
This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])-1))

next 12 Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]>=max('Period'[Period Rank]) && 'Period'[Period Rank]<=max('Period'[Period Rank])+12 ))

Very similar way we deal with week

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8