Forum Discussion
Helper IV12 month average on the same day
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
Helper IVmahoneypat Thank you very much for your help!!
Microsoft EmployeePlease try this one instead. I didn't notice before that you have the Month column in the visual (not dates).
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"))