Forum Discussion
Advocate IVGraph showing 6 weeks before and after date range
I am trying to create a graph in PowerBi Desktop that allows the user to select a period using two dates and then shows data for 6 weeks before and 6 weeks after that period as per the below graph. Is there anyway to do this in PowerBi?
I thought of using a filtered table, but obviously that won't work, since filtered tables are not dynamic. I think that I need some way of creating a measure that ignores external filters (the slicer) but keeps internal filters (the date axis) and also filters to the date range.
I got as far as getting a total for the period before (and, by the same logic the period after) but the measure ignores the interal filters (date axis) and I am not sure how to continue from here (I can't find an ALL function that would keep internal filters).
measure =
var min_date = DATEADD(FIRSTDATE(ALLSELECTED('Date'[Date])),-1,DAY)
return CALCULATE([value],ALL('Date'),DATESINPERIOD('Date'[Date],min_date,-42,day))
I've experimented with using a second date column in the date table but I haven't managed to get that to work. I also experimented with using the date column from the data table as the slicer and trying to selectively ignore filters but that doesn't work either.
OK, so I managed to solve this problem by using an independent date table and creating 2 measures:
period_value =
var min_date = FIRSTDATE(ALLSELECTED('Date2'[Date]))
var max_date = LASTDATE(ALLSELECTED('Date2'[Date]))
return IF(AND(min_date<=MAX('Date'[Date]),max_date>=MAX('Date'[Date])),[value],0)6wks_before_and_after =
var min_date = FIRSTDATE(ALLSELECTED('Date2'[Date]))
var max_date = LASTDATE(ALLSELECTED('Date2'[Date]))
return IF(OR(AND(min_date>MAX('Date'[Date]),DATEADD(min_date,-42,DAY)<=MAX('Date'[Date])),
AND(max_date<MAX('Date'[Date]),DATEADD(max_date,42,DAY)=>MAX('Date'[Date]))),
[total_sales_qty],0)These measures are for graphing purposes only. Not sure if anyone has a simpler solution?
2 Replies
EP1OK, so I managed to solve this problem by using an independent date table and creating 2 measures:
period_value =
var min_date = FIRSTDATE(ALLSELECTED('Date2'[Date]))
var max_date = LASTDATE(ALLSELECTED('Date2'[Date]))
return IF(AND(min_date<=MAX('Date'[Date]),max_date>=MAX('Date'[Date])),[value],0)6wks_before_and_after =
var min_date = FIRSTDATE(ALLSELECTED('Date2'[Date]))
var max_date = LASTDATE(ALLSELECTED('Date2'[Date]))
return IF(OR(AND(min_date>MAX('Date'[Date]),DATEADD(min_date,-42,DAY)<=MAX('Date'[Date])),
AND(max_date<MAX('Date'[Date]),DATEADD(max_date,42,DAY)=>MAX('Date'[Date]))),
[total_sales_qty],0)These measures are for graphing purposes only. Not sure if anyone has a simpler solution?
