Forum Discussion
Poorly calculated average
The first thing would be to create a calendar table ("New Table" under "Modeling" in the menu):
Calendario = VAR _MinDate = MIN ( BI_CM[FECHA] ) VAR _MaxDate = MAX ( BI_CM[FECHA] ) RETURN ADDCOLUMNS ( CALENDAR ( _MinDate, _MaxDate ), "MesNum", MONTH ( [Date] ), "Año", YEAR ( [Date] ), "Mes", FORMAT ( [Date], "MMM" ) )Once created, sort the "Month" column by the "MesNum" column.
Now create a relationship between the Calendar [Date] and BI_CM[DATE]
Use the fields in the calendar table in visuals, measurements, filters, etc...
For measurements:
Total 2020 = CALCULATE([TOTAL], FILTER(Calendario, Calendario [Año] = 2020))
And for the average:Promedio 2020 = AVERAGEX(Calendario, [Total 2020])
The first thing would be to create a calendar table ("New Table" under "Modeling" in the menu):
Calendario =
VAR _MinDate =
MIN ( BI_CM[FECHA] )
VAR _MaxDate =
MAX ( BI_CM[FECHA] )
RETURN
ADDCOLUMNS (
CALENDAR ( _MinDate, _MaxDate ),
"MesNum", MONTH ( [Date] ),
"Año", YEAR ( [Date] ),
"Mes", FORMAT ( [Date], "MMM" )
)
Once created, sort the "Month" column by the "MesNum" column.
Now create a relationship between the Calendar [Date] and BI_CM[DATE]
Use the fields in the calendar table in visuals, measurements, filters, etc...
For measurements:
Total 2020 =
CALCULATE([TOTAL], FILTER(Calendario, Calendario [Año] = 2020))
And for the average:
Promedio 2020 =
AVERAGEX(Calendario, [Total 2020])
Perfect. Now if it goes perfectly. Thank you so much for the help.
a greeting.