Forum Discussion

BI_devlpr's avatar
BI_devlpr
Regular Visitor
8 years ago

Include fields/columns sameperiodlastyear

Hi All,

 

I am trying to calculate last year value. I am using sameperiod last year to achieve this functionality. Keeping in mind that same period last year is a contiguous function, I am using a calendar table for time value. Now this measure gives me just the sum of date . I will have to include region, department and site level information. But my formula is immune to all these fields. I am unsure of what needs to be used here. Please help

 

 

so far I tried these

last_year = 
CALCULATE(AVERAGE('Overtime'[OT_Value]),
FILTER(all('Calendar'),'Calendar'[year]= 'Calendar'[OT LastYear Value]))

Over Time Last Year = 
CALCULATE(AVERAGE('Overtime'[OT_Value]),
DATEADD('Calendar'[calendar_date],-1,YEAR), ALLSELECTED('Overtime'[OT_Value]),
ALLEXCEPT('Calendar','Calendar'[calendar_date]))

OT Last Year = 
CALCULATE(AVERAGE('Overtime'[OT_Value]),
SAMEPERIODLASTYEAR('Calendar'[calendar_date]),
ALLEXCEPT('Calendar','Calendar'[calendar_date]))

 

5 Replies

    • BI_devlpr's avatar
      BI_devlpr
      Regular Visitor
      8 years ago

      Yes, my calendar table is at day level. The month/year column is from the calender table. All dates are joined using calendar key from other tables. I tried using the date column to join the tables. It did not help me. 

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Solution Sage
    8 years ago

    Hi BI_devlpr,

     

    In your scenario, you want to get the measure values also be based on region, department and site group. Right?

     

    Please try this, see if it helps:

     

    last_year =
CALCULATE (
    AVERAGE ( 'Overtime'[OT_Value] ),
    FILTER (
        ALL ( 'Overtime' ),
        'Overtime'[region] = MAX ( 'Overtime'[region] )
            && 'Overtime'[department] = MAX ( 'Overtime'[department] )
            && 'Overtime'[site] = MAX ( 'Overtime'[site] )
    ),
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[year] = 'Calendar'[OT LastYear Value] )
)

    By the way, if you can share us some sample data and the expected result. It'll help us a lot to provide more accurate solutions.

     

    Thanks,
    Xi Jin.

    • BI_devlpr's avatar
      BI_devlpr
      Regular Visitor
      8 years ago

      v-xjiin-msft I tried this one and it gave me a null value. When I am trying to use different functions, the formula just considers the sum of measure and date, it ignores the rest of it. Is it because sameperiod last year is a cintiguous function. Perplexed !! 

      • v-xjiin-msft's avatar
        v-xjiin-msft
        Solution Sage
        8 years ago

        Hi BI_devlpr,

         

        Then please share us more detailed information like some sample data which we can copy and paste directly and its corresponding expected result. So that we can make some tests rather than just guessing.

         

        Thanks,
        Xi Jin.