Measure with conditional date to blend YTD actuals and YTG forecast

[modelmike]

Total noob so bear with me 🙂

I have a union table, Volume_Weeks, structured like this.

Inside the type column, I have three categories actual, forecast, and budget.

I wish to create a measure that combines YTD actuals with YTG forecast. I understand this would be very easy if my data was not in a flat union table (i.e. separate columns for actuals + forecast) because I could use a simple calculated column. However, I'm relucant to to adjust the data model so I would like to accomplish this with a measure. 

I'm trying the following but I receive an error:

LE_FY = 
    var actualVol = CALCULATE(SUM(Volume_Weeks[value]),Volume_Weeks[type]="actual")
    var LEVol = CALCULATE(SUM(Volume_Weeks[value]),Volume_Weeks[type]="LE")
    var LE_FY_Vol = IF(Volume_Weeks[Week_Num] < WEEKNUM(TODAY()),              
        actualVol ,
        LEVol)
Return LE_FY_Vol

Error:

A single value for column 'Week_Num' in table 'Volume_Weeks' 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 [...]

I don't know enough about measures yet to understand how/if this can be resolved. Assistance is appreciated!

[popov · Accepted solution]

Hi, @modelmike

In your IF statemet you should be use this - MAX(Volume_Weeks[Week_Num]) < WEEKNUM(TODAY())

[modelmike]

Hi, @popov 

You solved it! Thank you. If you have some time, can you explain how this formula works? the MAX function is confusing to me because the MAX value in [Week_Num] is 52 (of course). So is the MAX function evaluating the MAX(Week_Num) for each row-combination in the table?

[popov]

Hello, @modelmike

MAX(Week_Num) evaluated for filter context, not for each row. When you start to use your measure in report, generate filter context and MAX(Week_Num) evaluating on currently visible rows of table.