Dynamic measure for last year based on selected values in time slicer

[Anonymous]

Hi guys, 

I am new to the community and I hope there is someone here who can help me out. 

I am trying to create a measure that calculates the percentage of invoiced hours out of total hours worked in the same period last year as the selected period in the slicer.

I originally created the following measure to calculate the percentage of hours invoiced up to todays date last year (YTDLY), which works fine: 

IP Last year = CALCULATE([Inovoiced Percentage];DATESBETWEEN('Common DimDate'[Date];DATE(YEAR(TODAY())-1;1;1);DATE(YEAR(TODAY())-1;MONTH(TODAY());DAY(TODAY()))))

However, my customer wants the measure to be dynamic, so that if she selects January to March 2018 in the time slicer, the measure will show the invoiced percentage of hours worked from January to March 2017. 

I tried to adjust the measure to the following, in order for it to take the selected dates in to account: 

IP Last year = CALCULATE([Inovoiced Percentage];DATESBETWEEN('Common DimDate'[Date];DATE(YEAR(FIRSTDATE('Common DimDate'[Date]))-1;1;1);DATE(YEAR(LASTDATE('Common DimDate'[Date]))-1;MONTH(LASTDATE('Common DimDate'[Date]));DAY(LASTDATE('Common DimDate'[Date])))))

Which provides the correct results when 2017 is selected in the slicer: 

However, if I set the start date to 01.01.2018 the measure shows up blank as 2017 is filtered out of the time slicer. It also does not work for other years than 2017/2018.

Is there a way I can get around this? Or another way to solve the problem? I thought about ALL() to ignore the filter, but I am not sure how to integrate it in my measure for it still to be dynamic.

I am using Direct Query Mode, so there are some limitations to which formulas I can use. 

Thanks in advance 🙂 

[v-yuta-msft]

Hi AstridN,

What's the range of date in column 'Common DimDate'[Date]? Please check if the range is not enough. Also, you can try measure below and check if it can meet your requirement.

IP Last year =
CALCULATE (
    [Inovoiced Percentage],
    DATESBETWEEN (
        'Common DimDate'[Date],
        DATE ( YEAR ( MIN ( 'Common DimDate'[Date] ) ) - 1, 1, 1 ),
        DATE ( YEAR ( MAX ( 'Common DimDate'[Date] ) ) - 1, MONTH ( MAX ( 'Common DimDate'[Date] ) ), DAY ( MAX ( 'Common DimDate'[Date] ) ) )
    )
)

Regards,

Jimmy Tao

[Anonymous]

Thanks for replying Jimmy,

The date range should be sufficient. 

I tried your DAX, but it gives the same results as my formula, where you only get 2017 when 2017 is included in the time range. I guess this is because the slicer filters the data posted in 2017 when 2017 is excluded. 

Regards, 

Astrid