cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
modelmike Frequent Visitor
Frequent Visitor

Measure with conditional date to blend YTD actuals and YTG forecast

Total noob so bear with me Smiley Happy

 

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

6-8-2018 11-47-46 AM.png

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
popov Member
Member

Re: Measure with conditional date to blend YTD actuals and YTG forecast

Hi, @modelmike

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

3 REPLIES 3
popov Member
Member

Re: Measure with conditional date to blend YTD actuals and YTG forecast

Hi, @modelmike

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

modelmike Frequent Visitor
Frequent Visitor

Re: Measure with conditional date to blend YTD actuals and YTG forecast

 

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 Member
Member

Re: Measure with conditional date to blend YTD actuals and YTG forecast

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.