Compare periods with nonstandard time tables.

[Anonymous]

Hello everyone,

I am tasked with developing a report which displays point of sale more efficently and intutivily than excel, so I've turned to Power BI. 

I am however stuck on the issue of time periods. This data does not utilize a standard date table as the periods are nonstandard. I have therefore tried to browse this forum and have seen some code like this below.

This is my time mapping table: 

With this code I try to bring a measure's value last year to this year. I have a column in my time mapping that adds 13 to the period number (13 intervals constitutes a year) which is called "period_LY". With the variable I try to create a table with the corresponding last year's periods based on a slicer (if one selects period 1 and 2 it should bring in period 14 and 15 to the variable). In the next line I try to apply a filter other than the one on the slicer to the measure, namely say that the period should equal the period LY. That is it should return period 14 if 1 is seleted in the filter. 

Dynamic Normal LY = 
VAR PeriodLY = ALLSELECTED('Time Mapping'[Period_LY])

RETURN
CALCULATE([Dynamic Normal], FILTER(ALL('Time Mapping'[Period]), 'Time Mapping'[Period] IN PeriodLY))

But this code returns this table:

Does anyone know how to achieve what I am trying to do?

Thanksful for any suggestion!

[amitchandak]

@Anonymous , I am assume Time is separate table like date

Based on period rank column

Period Rank = RANKX(all('Period'),'Period'[year period],,ASC,Dense) // YYYYPP format [Year]*100 +[Period]

example based on my tables

This period vs last period  and -13 for last year
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))

Last year Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])-13))

Till date, use = for one  period

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Period] <= Max('Date'[Period]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Period] <= Max('Date'[Period])))

only at the year level

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

[Anonymous]

Hej again @amitchandak ,

thanks for your reply

while this indeed did the job well, shifting the values to the approportiate period when i shifted to descend. 

However, this just sums the last year of the period with the highest rank that I select. I need it to be able to sum multiple periods. Is this possible somehow? 

Thanks in advance!