Filter out YTD Orders List table to only include rows created on or before a selected month?

[Rafal_Duzowski]

I have got a report that has 2 tables - Date and Orders. There is a page showing 2 slicers - Year and Month and 2 tables - list of orders for selected month and YTD.

Once a month is selected, both tables filter out data for selected month only (which is expected behavior).

So for YTD Orders List I switched off the interaction between Month slicer and YTD Orders list table (bottom table). Now YTD Orders List displays all months regardless of the selected month.

Is there a way to filter out YTD Orders List table to only include rows created on or before a selected month?

Columns in the table are table columns, not measures, so approaches with additional calendar table did not work.

I found a workaround to set Months slicer to multiselect but this is not user friendly or intuitive. Any suggestions?

[amitchandak]

@Rafal_Duzowski , if select a month and want to show trend of more than those, then slicer should be on an independent date table

then you can use measure like below with connected table

//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(sales[Gross Sales]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

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

[Rafal_Duzowski]

Hi @amitchandak , I was thinking about such approach but I am pulling columns straight from the tables, no measures at all.

Ideally, if I could set up a filter on a column of table visual e.g. DATE_CREATED on or before [Measure with Date] but it does not seem to be feasible in PowerBI, unfortunately.

SELECTEDVALUE on my Date column also does not work when interaction is off.

I also thought about independent date table approach but that would affect relationshions and/or force me to write a measure for each column in a table.

[Anonymous]

Hi @Rafal_Duzowski ,

First, the calculated column will not change according to the user interaction(slicer, filter, column selections etc.) in the report as the value of a calculated column is computed during data refresh and uses the current row as a context... Please review the following links about the difference of calculated column and measure...

Calculated Columns and Measures in DAX

Calculated Columns vs Measures

If you want to get the dynamic value base on the selected month, you can follow the steps below to get it:

1. Create a separeted date dimension table

2. Create a measure to judge which data fulfill the conditions

3. Apply a visual-level filter on the visual.

If the above one can't help you get the expected result, please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards