Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi!
I need to calculate the daily avarege of persons using the data of the week. The logic should be like this: the calculation starts TODAY() and calculates the average of the last 7 days (including today). This average should be the same for that week. Then it must calculate the same average for the previous week. The graph should look like this one.
I managed to do a rolling average with this formula
Media Tot Personas =
but I need the week average to be constant like the graph above.
By the way my data is all in the same table (date and total persons).
Hope I am being clear enough.
Thanks!
Solved! Go to Solution.
HI @mficco,
Perhaps you can add a condition in the expression to set up a rolling based on date field values:
Media Tot Personas =
VAR currDate =
MAX ( Fechas[Fecha] )
VAR _weeknum =
WEEKNUM ( currDate )
RETURN
CALCULATE (
AVERAGE ( Fechas[Tot Personas] );
FILTER (
ALL ( Fechas );
IF (
_weeknum > 1;
WEEKNUM ( Fechas[Fecha] ) = _weeknum - 1
&& YEAR ( Fechas[Fecha] ) = YEAR ( currDate );
WEEKNUM ( Fechas[Fecha] )
= WEEKNUM ( DATE ( YEAR ( currDate ) - 1; 12; 31 ) )
&& YEAR ( Fechas[Fecha] )
= YEAR ( currDate ) - 1
)
&& Fechas[Fecha] <= currDate
&& currDate <= TODAY ()
)
)
Regards,
Xiaoxin Sheng
HI @mficco,
I think you need to extract the current weeknum and year as a condition to group calculate instead of the current date or it will dynamic rolling records based on the current date.
Media Tot Personas =
VAR currDate =
MAX ( Fechas[Fecha] )
VAR _weeknum =
WEEKNUM ( currDate )
RETURN
CALCULATE (
AVERAGE ( Fechas[Tot Personas] );
FILTER (
ALL ( Fechas );
IF (
_weeknum > 1;
WEEKNUM ( Fechas[Fecha] ) = _weeknum - 1
&& YEAR ( Fechas[Fecha] ) = YEAR ( currDate );
WEEKNUM ( Fechas[Fecha] )
= WEEKNUM ( DATE ( YEAR ( currDate ) - 1; 12; 31 ) )
&& YEAR ( Fechas[Fecha] )
= YEAR ( currDate ) - 1
)
&& currDate <= TODAY ()
)
)
Regards,
Xiaoxin Sheng
Hi @v-shex-msft!
That's the idea. I need the average to dynamically roll whenever a day passes by. WEEKNUM() uses the week number of the year but I don't want the calendar week average. I need the week to roll, always starting form TODAY().
The quick solution I came up with was to make 3 measures (in order to show at least the last 3 weeks):
HI @mficco,
Perhaps you can add a condition in the expression to set up a rolling based on date field values:
Media Tot Personas =
VAR currDate =
MAX ( Fechas[Fecha] )
VAR _weeknum =
WEEKNUM ( currDate )
RETURN
CALCULATE (
AVERAGE ( Fechas[Tot Personas] );
FILTER (
ALL ( Fechas );
IF (
_weeknum > 1;
WEEKNUM ( Fechas[Fecha] ) = _weeknum - 1
&& YEAR ( Fechas[Fecha] ) = YEAR ( currDate );
WEEKNUM ( Fechas[Fecha] )
= WEEKNUM ( DATE ( YEAR ( currDate ) - 1; 12; 31 ) )
&& YEAR ( Fechas[Fecha] )
= YEAR ( currDate ) - 1
)
&& Fechas[Fecha] <= currDate
&& currDate <= TODAY ()
)
)
Regards,
Xiaoxin Sheng
@mficco , if you need same Average for the week then you can not do rolling 7 days.
You need have date/week table with week rank column
COlumn
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
And formula like this. Plot this with week from week/date table
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Tolling 7 would be like this, but that will change on day to day basis - Rolling 3 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-3,Day))
Sorry @amitchandak , I think I am not following this solution.
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
what do you mean by "Week start date"? I ranked the dates using this formula:
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |