cancel
Showing results for
Did you mean:
Helper II

## Time intelligence on non-standard calendar table

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?

1 ACCEPTED SOLUTION
Community Support

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

Community Support

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