Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Measure using CALCULATE not working

I'm trying to create a measure to calculate revenue (SubGrossAgencyTotRev) for the same week last fiscal year. MaxDate is the week-ending date. I added two columns to our date table: FiscalWeekYear & FiscalPYWeekYear. Here is the measure:

PY WTD =
    VAR WENo = LOOKUPVALUE('dim CalendarDate'[FiscalPYWeekYear],
    'dim CalendarDate'[CalendarDateKey],[MaxDate])
    VAR Result =
    CALCULATE(
        [SubGrossAgencyTotRev],
        'dim CalendarDate'[FiscalWeekYear]= WENo
    )
    RETURN Result
 
Unfortunately, the result is (Blank). Is it something with the way I've created the measure?

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Anonymous It's really difficult to say but CALCULATE can return wonky results sometimes. Perhaps if you post sample data and the formula for your measure can be more specific.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks for your reply and the video, Greg. I tried switching to using SUMX instead:

      PY WTDv2 =
          VAR WENo = LOOKUPVALUE('dim CalendarDate'[FiscalWeekOfYearNumber],
          'dim CalendarDate'[CalendarDateKey],[MaxDate])
          VAR YrNo = LOOKUPVALUE('dim CalendarDate'[FiscalYearNumber],
          'dim CalendarDate'[CalendarDateKey],[MaxDate]) - 1
          VAR Result =
          SUMX(FILTER('dim CalendarDate','dim CalendarDate'[FiscalWeekOfYearNumber]=WENo && 'dim CalendarDate'[FiscalYearNumber] = YrNo),[SubGrossAgencyTotRev])
          RETURN Result
       
      It's still doing the same thing. It's a ton of data, so I'm not really sure how to share it. I feel like the problem might be related to the YrNo variable. However, I created measures exactly the same way I defined the variables to see if maybe that was the issue. When I look at them, it looks right. So, I think the variables are defined correctly. Also, when I filter all my visuals using the fiscal week number and fiscal year defined in the variables, there is data. So that's not the issue either.  I'm not sure what else to try.