Custom date periods together with SamePeriodLastYear

[andsil]

Need help!

I'm trying to create a report showing different facts for current year with some last year comparisons (in this example SamePeriodLastYear is used or simplification). The aim is to let the end user through a slicer (list) choose between different cusomized date periods, such as for example "Last week, Week to Date, Last 3 days" etc (about 20 different pre-defined periods). So the aim is to not need to use the Relative view in the standard slicer, as it does not provide all needed periods, and when using that method the user needs to manually enter different combinations which is not optimal.

The big challenge for me is that the data for last year is not showing when choosing a period from a period dimension table. It only works when I enter periods through a slicer containing dates from the date dimension table (using visualization "Slicer" and setting Relative).

In my simplified example pbix the relationships looks as follows:

To get the filtering of custom periods to filter the dates, the DIM-Periods table needs to be set to bidirectional in the cross filter direction in its relationship to the DIM-Dates. Otherwise filtering periods will have no impact on dates and facts.

When filtering the date dimension the "manual" way using dates from table DIM-Dates, it works ok:

But when using the Period filter with data from table DIM-Periods the LY gets blank:

I have tried to setup period dimension tables both through M and DAX, both learned from this post: https://blog.crossjoin.co.uk/2016/05/30/creating-current-day-week-month-and-year-reports-in-power-bi... But same challenge both ways as the outcome to table DIM-Periods gets the same.

In this example I'm using the DAX version, which says:

DIM-Periods (DAX) =

VAR DateTable = CALENDAR ( Date ( 2018;1;1) ; Date( 2019;12;31) )

VAR Today = ADDCOLUMNS(FILTER(DateTable;DATEDIFF(TODAY();[Date];DAY) = 0);"Period";"Today";"Sort";1)

VAR Yesterday = ADDCOLUMNS(FILTER(DateTable;DATEDIFF(TODAY();[Date];DAY) = -1);"Period";"Yesterday";"Sort";2)

VAR LastWeekday = ADDCOLUMNS(FILTER(DateTable;DATEDIFF(IF(WEEKDAY(TODAY();2)=1;TODAY()-3;TODAY()-1);[Date];DAY) = 0);"Period";"LastWeekday";"Sort";3)

VAR ThisWeek = ADDCOLUMNS(FILTER(DateTable;DATEDIFF(TODAY();[Date]-1;WEEK) = 0);"Period";"ThisWeek";"Sort";4)

VAR LastWeek = ADDCOLUMNS(FILTER(DateTable;DATEDIFF(TODAY();[Date]-1;WEEK) = -1);"Period";"LastWeek";"Sort";5)

Return

UNION(Today;Yesterday;LastWeekday;ThisWeek;LastWeek)

Does anyone have a solution for using cusomized periods with bidirectional filtering together with last year comparisons such as SamePeriodLastYear? Or any other solution to get to the same goal?

[v-chuncz-msft · Accepted solution]

@andsil 

You may apply virtual relationship in DAX measure.

https://community.powerbi.com/t5/Desktop/Use-a-date-slicer-to-filter-on-a-period-instead-of-a-single...