Forum Discussion

StefanoP's avatar
StefanoP
Regular Visitor
3 years ago

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

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.

 

 

 

10 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion

    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