Filter date table to display only a certain date range

[majman]

Hello all,

I need some help please in applying a filter to a date table so in my pivot chart I only displays the selected date range.

I have a measure to calculate rolling 12 month sales:
Rolling:=CALCULATE([TY Sales],ALL('TW'),FILTER(ALL('TW'),'TW'[to_date]>=MAX('TW'[to_date])-357&&'TW'[to_date]<=max('TW'[to_date])))
This works fine and calculates my rolling 12 month sales for each week correctly (as my sales data in actually in weeks).

I have joined my 'TW' (tradeweek) table to another tradeweek table that only contains the last 52 weeks that I want to display and this shows me the correct data in the chart. I want to show a rolling 52 weeks on the chart. I can use the YEAR column in my TW table but this will only show the weeks in this year (35 so far for FY14) or 87 weeks if I include FY13 as well.

What I want to do is filter this second TW table dynamically (maybe via a disconnected table) so:
1) it will always have the latest dates in the table (when refreshed from SQL), and
2) the user can select the latest 52 weeks or 104 weeks etc.

I understand how to use FILTER in a CALCULATE expression as per the formul above but how do I use it to filter the dates to display?

Any help would be greatly appreciated.

Thanks
Mark

 

[majman]

OK a bit of self help and I can sort of see how to fix this problem. If I add a calculated column to return "1" if I want to show the date and "0" if I don't I can acheive what I require (filtering the dates to display on my pivot chart)
So the following formula works OK, I get "1" for 02/07/2013 and "0" for 30/06/2013.

=if([to_date]>="01/07/2013"+0,1,0)

However this is not dynamic (I don't want a fixed date I want to be able to choose the date), so I have a disconnected table and have the following formula
=IF([to_date]>=DATEVALUE([TextDate]),1,0)

My disconnected table has the dates and a Measure for Start Date to allow the user to select that period they require and a measure to convert the Start Date to text. But I get "1" for all rows for [to_date]. It is as if the IF() does not recognise the DATEVALUE([TextDate]) as it should.

Does anyone have any ideas about how to get this second formula to work?

Thanks
Mark

Hello all,

I need some help please in applying a filter to a date table so in my pivot chart I only displays the selected date range.

I have a measure to calculate rolling 12 month sales:
Rolling:=CALCULATE([TY Sales],ALL('TW'),FILTER(ALL('TW'),'TW'[to_date]>=MAX('TW'[to_date])-357&&'TW'[to_date]<=max('TW'[to_date])))
This works fine and calculates my rolling 12 month sales for each week correctly (as my sales data in actually in weeks).

I have joined my 'TW' (tradeweek) table to another tradeweek table that only contains the last 52 weeks that I want to display and this shows me the correct data in the chart. I want to show a rolling 52 weeks on the chart. I can use the YEAR column in my TW table but this will only show the weeks in this year (35 so far for FY14) or 87 weeks if I include FY13 as well.

What I want to do is filter this second TW table dynamically (maybe via a disconnected table) so:
1) it will always have the latest dates in the table (when refreshed from SQL), and
2) the user can select the latest 52 weeks or 104 weeks etc.

I understand how to use FILTER in a CALCULATE expression as per the formul above but how do I use it to filter the dates to display?

Any help would be greatly appreciated.

Thanks
Mark

 

[butlerrbrian]

How about =IF([to date]>=TODAY()-365+0,1,0).