12 month average on the same day

[yugofukuda]

Hi,

For calculating a forecast, I would like to create the average of % on the same working day of last 12 months.

I would be appreciated if you could help me. Thanks!!

[mahoneypat]

Please try this expression

Avg Same WD =
VAR thisWD =
    MIN ( 'Date'[WD 22] )
VAR thisdate =
    MIN ( 'Date'[Date] )
RETURN
    AVERAGEX (
        FILTER (
            ALL ( 'Date'[Date] ),
            'Date'[Date] <= EOMONTH ( thisdate, 0 )
                && 'Date'[Date] >= EOMONTH ( thisdate, -12 )
                && 'Date'[WD 22] = thisWD
        ),
        [Percent Measure]
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[yugofukuda]

@mahoneypat 

Thank you for your quick response!!

I don´t know why but i can´t put "Calendar WD22" in the part below. How should I do??

Avg Same WD =

VAR thisWD =

MIN ('Calendar'[WD 22])

VAR thisdate =

MIN ( 'Calendar'[Date] )

RETURN

AVERAGEX (

FILTER (

ALL ( 'Calendar'[Date]),

'Calendar'[Date] <= EOMONTH ( thisdate, 0 )

&& 'Calendar'[Date] >= EOMONTH ( thisdate, -12 )

&& 'Date'[WD 22] = thisWD

),

'Daily Collection'[Collection %]

)

[mahoneypat]

My bad.  Forgot about that column.  Try this version.

Avg Same WD =

VAR thisWD =

MIN ('Calendar'[WD 22])

VAR thisdate =

MIN ( 'Calendar'[Date] )

RETURN

AVERAGEX (

FILTER (

ALL ( 'Calendar'[Date], 'Calendar'[WD 22]),

'Calendar'[Date] <= EOMONTH ( thisdate, 0 )

&& 'Calendar'[Date] >= EOMONTH ( thisdate, -12 )

&& 'Date'[WD 22] = thisWD

),

'Daily Collection'[Collection %]

)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[yugofukuda]

@mahoneypat 
Thank you very much but It seems to me that it don´t work well.  There are something that I should change??

[mahoneypat]

Please share the Collection % measure.

Regards,

Pat

[yugofukuda]

@mahoneypat 

Collection % = 'Daily Collection'[Collection for forecast]/calculate(sum(collection),all('Calendar'[WD 22]))

Collection for forecast = TOTALMTD(sum(collection),'Calendar'[Date])

Regards,

[mahoneypat]

Signing off the the night.  Will take a look again tomorrow, if someone else doesn't solve it first.

Regards,

Pat

[yugofukuda]

@mahoneypat  Thank you very much for your help!!

[mahoneypat · Accepted solution]

Please try this one instead.  I didn't notice before that you have the Month column in the visual (not dates).

Collection % Last 12 Months =
VAR thisdate =
MAX ( 'Calendar'[Date] )
VAR last12months =
CALCULATETABLE (
VALUES ( 'Calendar'[Month] ),
ALL ( 'Calendar' ),
'Calendar'[Date] <= EOMONTH ( thisdate, 0 ),
'Calendar'[Date] >= EOMONTH ( thisdate, -11 )
)
RETURN
AVERAGEX ( last12months, [Collection %] )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[yugofukuda]

@mahoneypat 

Thank you very much!! It woked well!! you are so smart.

Sorry, could you do me a favor?? (other matter)

For calculating forecast, I created the expression as below but I don't know how to insert today´s WD22 automatically (for example, today´s wd22 is 17.  I tried to use calculate or lookupvalue but it didn´t work well.
Could you help me??
 

For Forecast =

CALCULATE('Daily Collection'[Collection % Last 12 Months],

'Calendar'[WD 22]=17  *I would like to put 17 automatically. 

,'Calendar'[Month]=format( DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())),"YYYY-MM"))

[Greg_Deckler]

@yugofukuda - You can create this column:

IsWorkingDay = IF(WEEKDAY([Date],2) < 6,1,0)

And then this column will return the working day number of every day in each month:

WorkingDay# = 
    VAR __WorkingDay = COUNTROWS(FILTER('Calendar',YEAR([Date])=YEAR(EARLIER([Date])) && MONTH([Date])=MONTH(EARLIER([Date])) && [Date]<=EARLIER([Date]) && [IsWorkingDay]=1))
RETURN
    IF([IsWorkingDay]=0,0,__WorkingDay)

PBIX is attached below sig.