Time intelligence on non-standard calendar table

[LamSar]

Good afternoon,

I have a requirement. 

I need to calculate sales per stockperiod and per stockperiod-1 (i.e. previous stockperiod).

I have the below table:

This table holds the stockperiod, de min and max date per period and the revenue.

Now I need to create a measure that calculates the revenue for the previous stock period. 

i.e. on the row of StockPeriod 2021/01, I need to calculate the revenue for stockPeriode 2020/12. I can't use the standard time intelligence functions for this. 

Any ideas? 

[v-yangliu-msft · Accepted solution]

Hi  @LamSar ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

Year =
YEAR('Table'[Date])

Month = MONTH('Table'[Date])

2. Create measure.

Measure =
var _mindate=
DATE(
    MAX('Table'[Year]),MAX('Table'[Month])-1,1)
var _maxdate=
EOMONTH(
    _mindate,0)
return
SUMX(FILTER(ALL('Table'),'Table'[Date]>=_mindate&&'Table'[Date]<=_maxdate),[Period])

Flag =
IF(
    MAX('Table'[Date])=
MINX(FILTER(ALL('Table'),'Table'[Year]=MAX('Table'[Year])&&'Table'[Month]=MAX('Table'[Month])),[Date]),
1,0)

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly