Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi
I am using the following expression on a measure on a pivot table.
sum({$<[EventDate.CalendarMain.Date]={">=$(=Date(Today()-6))<=$(=Max(EventDate))"}>}[Sales])
however I need to be able to select an event date from a filter pane, and the pivot table will thus show - 6 days from the selected event date.
I have tried to use
getselectedcount within a sort expression on EventDate , I cannot get this to work.
If( GetSelectedCount(EventDate), Max(FinancialYear), (EventDate)-6) )
please help
Hi Joanna, can you try this?:
sum({$<[EventDate.CalendarMain.Date]={">=$(=Date(Max(EventDate)-6))<=$(=Max(EventDate))"}>}[Sales])
Have you tried this:
Sum({$<[EventDate.CalendarMain.Date]={">=$(=Date(If(GetSelectedCount(EventDate), Max(FinancialYear), Max(EventDate)-6)))<=$(=Max(EventDate))"}>}[Sales])
Hi
these above don't work
there's nothing wrong with my calculation
its just when i need to select a different event date from the filter pane, i would like the pivot table to select the next 6 days previous to date chosen in filter pane.
i need to place something in the event date sort by expression like
If( GetSelectedCount({<[EventDate.CalendarMain.Date]={">=$(=Date(Today()-6))<=$(=Max(EventDate))"}>}EventDate), Max(FinancialYear), EventDate)
but i cannot get it to work
please help
Hi Joanna, Today()-6 is 23/07/2016, if you have a fixed "Today()-6" and the user select some date before that one it will show zero, because there will be no date upper than 23/07 and lower at the same time, that's why you need to use the selected date in both places (>= and <=).
About whyit doesn't works I will need a sample to check date formats.
Hi
I need the chart as soon as you load the sheet show last 6 days...then if a user choses a event date, it will show the data for 6 days from the chosen date
I don't get 0 I just get the count for the single day (EventDate) chosen
please help
Hi Joanna, if you select '01/06/2016' (or any other date of june) as filtered event date it shows one day? how can any date accomplish the condition >=27/07/2016 and <=01/06/2016?
I will need a sample to check and give an answer.