Use slicer value to filter a table

[PSampras]

Hy everyone,

this is my first post here 😃 I hope to write it in the right way

This is my problem.

I have a simple "Table 1 - Fatturato"

I want to create an other table "Table 2 - Fatturato progressivo" containing only first n months of the year.
I create a calculatetable, named TbProgressivo, and i got it.

TbProgressivo = CALCULATETABLE(FACT_PL_FATTURATO,year(FACT_PL_FATTURATO[DatDoc])=2022 && month(FACT_PL_FATTURATO[DatDoc])<=3)

This is the result i want.

But i got it inserting manually 2022 as year and 3 as month.

I want to read these 2 values (year and month) from respective slicers

With this measure, i can read the slicer value

MeseSelezionato = SELECTEDVALUE(CALENDARIO[MonthOfYear])

But when i use it in calculatetable

TbProgressivo = CALCULATETABLE(FACT_PL_FATTURATO,year(FACT_PL_FATTURATO[DatDoc])=2022 && month(FACT_PL_FATTURATO[DatDoc])<=SELECTEDVALUE(CALENDARIO[MonthOfYear]))

Result is a empty table

I think this a relation problem or some issue with calendar table.

But i can't figure out...

This is the relations:

File pbix: https://drive.google.com/file/d/1inh92l7sQzFfXJYuu6D2scuRlvPoJWTK/view?usp=sharing

Thanks for the help!!

Giuseppe

[v-eqin-msft · Accepted solution]

Hi @PSampras ,

To my knowledge, calculated table could not be dynamically changed via slicer.

If you want to dynamically filter a table... I‘d suggest you put the columns into a table visual and create a measure that will return 1 for the rows you want to see and 0 for those that you don't. Then use the Visual-level Filter Pane to filter the rows by the measure. For example:

Flag Filter = IF(YEAR(MAX('Fatturato'[DatDoc]))=SELECTEDVALUE('Year Selection'[Year]) && MONTH(MAX('Fatturato'[DatDoc]))<=SELECTEDVALUE('Month Selection'[Month]),1,0)

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

[PSampras]

Thank you @v-eqin-msft , it works!

[amitchandak]

@PSampras , if you select one month and want to display more than that then your slicer need to be on an independent date table

example

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

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

[PSampras]

I need to create a relation between Date and Date1?
Thank for your reply and your support.

[PSampras]

@amitchandak 
Your formula works great: using CALENDARIO2 as indipendent data table, i obtain the progressive value related to the month selected in slicer ("Fatturato progressivo").
6.632.458,12 is the sum of gen, feb and mar. Cool!

But i can't understand how to use your kindly advice to obtain a table like this (Table2 in the figure)

I use:

Table2 =

CALCULATETABLE(FACT_PL_FATTURATO,CALENDARIO[Year]="2022" && CALENDARIO[MonthOfYear]<4)

and it works but, obviously, i have to replace 2022 and 4 with slicers values.

Thank you,
Giuseppe