Show last 2 years by slicer

[shrutik]

Hey,
I need to create a stacked bar chart where year and month slicer is there and chart will show last 2 year based on year selection i.e if 2022 is selected then data for 2021 and 2022 should be shown so in x-axis 2021 and 2022 will be there in different columns.Also month slicer setting is such that data is calculated for that month.Also there is a column legend for size.I do have a date table in relationship with main table as one to many.

Any ideas on how I can get this to work in Power BI ?

Thanks in advance,
Shruti

[v-yangliu-msft]

Hi  @shrutik ,

Here are the steps you can follow：

1. Create measure.

Flag =
var _selectyear=
SELECTEDVALUE('Date'[Date].[Year])
var _selectmonth=
SELECTEDVALUE('Date'[Date].[MonthNo])
var _laststart2=
DATE(
    _selectyear -1,1,1)
var _lastend2=
EOMONTH(
DATE(
    _selectyear-1,_selectmonth,1),0)
var _laststart1=
DATE(
    _selectyear ,1,1)
var _lastend1=
EOMONTH(
DATE(
    _selectyear,_selectmonth,1),0)
return
IF(
    AND(MAX('Table'[Date]) >=_laststart2 ,MAX('Table'[Date]) <=_lastend2)
    ||
    AND(MAX('Table'[Date]) >=_laststart1 ,MAX('Table'[Date]) <=_lastend1) ,1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[shrutik]

Hii,
This does not works when tables are in a relationship.Is there a way for it to work when relationships are there?

[amitchandak]

@shrutik , for that the slicer needs to be on an independent date/year table

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Year])
var _min = _max -1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Year] >=_min && 'Date'[Year] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI