Average % of year over the last five years

[MHoogeveen]

I have got a measure called [Result], the table this measure is based on is connected with a Date table with a Date column, [Date].[Date]. Given that today is 21st of june 2022, I would like to calculate the following:

• Average production % of entire year in the same period for the last 5 years:
  [Result] for 01-01 until 06-21 divided by [Result] for the entire year
  Example: 2021-01-01 until 2021-06-21 has a result of 240, entire year has 500; so outcome is 0,48
  Let's say the results for 2016-2021 are:
  

  Year	01-01 until 06-21	Entire Year	%
  2017	x	x	0.42
  2018	x	x	0.54
  2019	x	x	0.50
  2020	x	x	0.46
  2021	240	500	0.48
  		Average %	0.48

• I would like to divide my [Result] for the current year so far (so until 2022-06-21) with the given average in order to estimate my [Result] for the entire year, given patterns from the last 5 years. So if my result for 2022-01-01 until 2022-06-21 would be 330, my outcome would be 330 divided by 0.48 resulting in 687.5.

I have no idea how to get started. This is way above my DAX knowledge. Can you please help me with this case?

Please let me know if I need to provide any other data or to clarify anything.

[Anonymous · Accepted solution]

Hi @MHoogeveen ,

Please create these four measures.

TD = 
VAR _year = YEAR(MAX('Date'[Date]))
VAR _month = MONTH(TODAY())
VAR _day = DAY(TODAY())
VAR _result = 
CALCULATE(
    [Result],
        FILTER(
            ALL('Date'),
                'Date'[Date] >= DATE(_year,1,1)
                    && 'Date'[Date] <= DATE(_year,_month,_day) 
                        && _year <> YEAR(TODAY())
        )
)
RETURN
_result

% = 
AVERAGEX(SUMMARIZE('Date','Date'[Year],"M1",[YTD],"M2",[Result]),DIVIDE([M1],[M2]))

YTD = 
CALCULATE(
    [Result],
    DATESYTD('Date'[Date])
)

Expected values = 
DIVIDE([YTD],[%])

Attached PBIX file for your reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[MHoogeveen]

Thank you very much @Anonymous , took me a little while to check against my own results but your solution fits perfectly!