Creating a calculated table by passing a measure or parameter

1
votes

I have a requirement where I have a data like this,

Date      Name  Age 
1-1-2018  A     1
2-2-2018  B     1
3-3-2018  B     1
6-6-2018  C     2
7-7-2018  B     6

I am trying to give a slicer to the user to select the required number of months from the last month.

So to do that, I am using a calculated column like this:

Month Year = DATEDIFF((Table1[Date]), TODAY(), MONTH) + 1

So that changes the data to something like this:

Date      Name  Age  MonthYear
1-1-2018  A     1    7
2-2-2018  B     1    6
3-3-2018  B     1    5
6-6-2018  C     2    2
7-7-2018  B     6    1

The user selects the Month Year from the Slicer.

For example, when he selects 2, I want to display the last 2 months records in the table.

Expected Output:

Date      Name  Age  
6-6-2018  C     2   
7-7-2018  B     6

This works for me if I hardcode it like this:

Calculated Table = CALCULATETABLE(Table1,
                       FILTER(Table1, OR(Table1[MonthYear] > 2, Table1[MonthYear] = 2)))

But it fails when I try to pass the value in the place of 2 dynamically through a measure using SelectedValue function.

1
powerbidaxpowerbi-desktop

1 Answers

4
votes

Calculated columns and calculated tables cannot reference a slicer value since they are only computed when you load your data.

If you want to apply this filtering to a visual, I'd suggest creating a separate table for your slicer. For example, you could use Months = GENERATESERIES(1,12) and then rename the column Months as well.

Use the Months[Months] column for your slicer and then create a measure which references it to filter your table/matrix visual. 

Filter = IF(SELECTEDVALUE(Months[Months]) >= MAX(Table1[Month Year]), 1, 0)

Then use that measure in your Visual level filters box:

Output