Forum Discussion
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
Community 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