topic Re: It just won't work! HELP in DAX Commands and Tips

It just won't work! HELP

Anonymous — Fri, 02 Aug 2024 02:53:47 GMT

'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!

Re: It just won't work! HELP

Joe_Barry — Fri, 02 Aug 2024 05:04:28 GMT

Hi Anonymous

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

Re: It just won't work! HELP

Anonymous — Fri, 02 Aug 2024 12:27:02 GMT

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)