It just won't work! HELP

[tracy_m]

've built all of 1 simple dashboard in my life, and now my brand new boss has asked me to put together a similar dashboard to this image, I've created it in Excel but I need to put it into a BI dashboard that executive leadership can manipulate.  

I have very much similar data, with no prior year and forecasted/actual data, pulled on a monthly basis. I'm struggling on the running average (to replace cumulative) depending on the month selected. I have 2 different tables with actual and forecasted data (each criteria is a separate row with month).  

I've tried to use solutions found in other posts on here (Average YTD Calculation Based on User Selection)

Average Planned YTD =
VAR SelectedPeriod =
    SELECTEDVALUE ( 'Table'[Period] )
VAR MaxPeriod =
    MAX ( 'Table'[Period] )
RETURN
    IF (
        SelectedPeriod = BLANK (),
        [Planned (YTD)] / MaxPeriod,
        [Planned (YTD)] / SelectedPeriod
    )

 and have watched Youtube after Youtube videos and I'm still missing something.  I just need to be able to show that by selecting May, it'll calculate (by measure) the YTD average for actual and forecasted, which I can then setup conditional formatting for the variance.  Any guidance would be extremely helpful!  

[tracy_m]

Hi Joe, thank you so much for your quick response! I'm running into an issue with [Actual], I created a FACT table with the actual/forecast values, but when I try to use 

'FACT_Combined'[Actual], it gives me an error:  A single value for column 'Actual' in table 'FACT_Combined' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

it's part of an Average formula, so I'm not sure why it won't average the data in that column (although that's not what I want either, but it's a start)

[Joe_Barry]

Hi @tracy_m 

Do you need a cumulative value for the current year and it resets when the next year starts? The below measure with calculate the Average based on the Month chosen. You will need a Date table (See my bio below)

Avg. Actual = 
VAR NumOfMonths =
    MAX ( 'DIM Date'[Month] )
VAR LastCurrentDate =
    MAX ( 'DIM Date'[Date] )
VAR Period =
    DATESINPERIOD ( 'DIM Date'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
    CALCULATE (
        AVERAGEX ( VALUES ( 'DIM Date'[Month-Year] ), [Actual] ),///YOUR MEASURE HERE
        Period
    )
VAR FirstDateInPeriod =
    MINX ( Period, 'DIM Date'[Date] )
VAR LastDateWithSales =
    MAX ( 'CustomerSat'[Date] ) /// YOUR TABLE AND RESULT DATE HERE
RETURN
    IF ( FirstDateInPeriod <= LastDateWithSales, Result )

Hope this helps

Joe