Include fields/columns sameperiodlastyear

[BI_devlpr]

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]))

 

[v-xjiin-msft]

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]

@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]

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.

[MattAllington]

Is your calendar table at the day level?

is the month/year column in your visual from the calendar table?

More background here https://exceleratorbi.com.au/power-pivot-calendar-tables/

[BI_devlpr]

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.