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 &...
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.