Table with computed values that populate a measure table based on 2 date filters

[StefanoP]

Hello there, I'm a fresh approacher to this world of PowerBi and its related products and functions.
Even if I understand ( more or less ) how it works and what to do, I'm stuck in "how to do it".

I need to compute this :

a table of 12 measures with 12 columns ( one for each month of the year ) and a single row that contains a scalar.

This scalar is computed using the scalar associated on that month of the year + same on previous month + same on next month, all divided by 3.

As an example, JanValue is determined by this : [Jan(year)+Dec(year-1)+Feb(year)]/3

or more generally MonthValueScalar = [Month(period)+Month(period-1)+Month(period+1)]/3

where period is the pivot month from Jan to Dic of a selected year ( maybe with a slicer ) and is formed as YYYYMM as a number field.

I got 2 tables, CalendarVolumes and DLCalendar

Scalars needed are in CalendarVolumes, where DLCalendar contains period and year.

They're in relationship DLCalendar(period)-->CalendarVolumes(Period) as 1 to n.

I draw this kind of resolution but i'm stucking with the implementation.

1-Filter CalendarVolumes from year-1 to year+1 ( first filter based on year and second on periods )
2-use 3 variables for pivoting the scalars as period, prev_period, next_period ( with if branches in case pivot month is dec or jan )
3-compute the table based on pivot period

I need this computed table as a table of measures since values are updated by a refresh, also selected by slicer in reports.

So, I made this sketch solution :

***create the needed table***
CREATETABLE ( ActualTable, Jan to Dec columns )

***based on this filter by year***
FILTER ( CalendarVolumes,

***select year from DLCalendar***
FILTER ( DLCalendar, year-1 to year+1 )

every help is appreciated.

[tamerj1]

Hi @StefanoP 

this is a simple 3 months moving average. There is no need to create a measure for each month. I mean this us DAX, you can play with the filter context the way you want. 
a snapshot of your dimCalendar table will be helpful to find a solution suitable to this case

[StefanoP]

Here is the screen of the issue table

 

[tamerj1]

@StefanoP 

I meant the DLCalendar(period)

[StefanoP]

Honestly, period in DLCalendar can be taken out of computation, since all periods that matters are in CalendarVolumes, but here we are :

 

[tamerj1]

@StefanoP 
First add the following calculated column in the DLCalendar table

Period Rank =
RANKX ( DLCalendar, DLCalendar[Period],, ASC, DENSE )

Then use the following measure

3 Months Moving Average =
VAR CurrentPeriodRank =
    MAX ( DLCalendar[Period Rank] )
RETURN
    CALCULATE (
        SUM ( ActualTable[Value] ),
        DLCalendar[Period Rank] <= CurrentPeriodRank,
        DLCalendar[Period Rank] > CurrentPeriodRank - 3,
        ALL ( DLCalendar )
    ) / 3

[StefanoP]

Thank you very much, now I understand better.

Tell me if I get it right :

With period rank you obtain an added column to index periods.

With 3 months moving average, CurrentPeriod is a var set to max period based on a slicer, then return a table where the value is find and added on itself by using periodrank correlation in a fork between a max ( period rank ) and max-3 period.

Thank you for you support.

[tamerj1]

@StefanoP 
Correct

[StefanoP]

I got this counter intuitive results :

For the whole year selected for 2022 i got only 2 months drawing, instead for 2023 a whole year.

See here

 

 

[tamerj1]

@StefanoP 
There is nothing in the code that filters only 2 months. To be honest I don't understand the example you have presented. In this case I would kindly request you to provide sample data of all related tables along with the relational data model details and relationships. Also the DAX for the measure that you're trying to calculate it's 3 months moving average. You may use dummy data but please try not to over simplify. 

[StefanoP]

Hello everyone, I fixed it by changing slicer settings.

I'll update you if there will be something wrong.

Thanks for all your help and dedication.