Hi
Please tell me how can I fetch multiple values from a filter pane . I have a accounting period filter from which I want to calculate a certain measure of the previous selected period.
For example if I select counting period as 4,5 it should return me sum(revenue) for 4 and 3 month.
Thanks in advance
Try this
=Sum({<[counting period]={$(=Concat(Distinct [counting period]-1,','))}>}revenue)
Hi Dave,
Its working well only its not giving me any data for 1 counting period
I am using the following expression
if(H1='Previous Completed Quarter', Sum({<Quarters={$(=Concat(Distinct Quarters-1,','))} ,[Accounting Period]={$(=Concat(Distinct [Accounting Period]-3,','))}>}[Service Rev]),
if(H1='Recent Completed Quarter',sum({$}[Service Rev]))
))
where h1 is the header and quarters are fetched from accounting period as follows.
quarters accounting period
1 1,2,3
2 4,5,6
3 7,8,9
Problem is data is showing correctly for quarter>1 , but for quarter 1 no data is being shown
because when you select quarter 1 it will calculate 'quarter 0' which is not exist
what data do you want to show for 1 counting period ?
Hi Dave this is the view I am trying to create , yes I agree that previous data for quarter 1 should not reflect, but it should show me quarter 1 data under recent completed quarter.
I don't think 'H1' makes sense,you may need to get rid of it ,just go with two expressions like this
expression for previous completed quarter :
Sum({<Quarters={$(=Concat(Distinct Quarters-1,','))}[Service Rev])
expression for recent completed quarter :
Sum([Service Rev])
Hi Dave,
My requirement consists of multiple headers thus I have to make inline headers. I cannot use differnet measures for previous and current.
ok , then we can combine the two expressions and try
IF(H1='Previous Completed Quarter',Sum({<Quarters={$(=Concat(Distinct Quarters-1,','))}[Service Rev]),Sum([Service Rev]))
you may need to attach your qvf file if it still doesn't work
Quarter 1 data is still not showing up . Don't know how to proceed. Expected output should be like this.
Thanks dave looked into the wrong file..:)... But not able to change data by altering accounting period, when I change the expression to this
IF(H1='Recent Completed Quarter' ,Sum([Service Rev]),Sum({<Quaters={$(=Concat(Distinct Quaters-1,','))}, [Accounting Period]={$(=Concat(Distinct [Accounting Period]-3,','))}>}[Service Rev]))
No data shows up under Q1 . Using your expression I am able to get Q1 under recent but when I change the accounting period(month) under a quarter ,it shows null value. I want that when I select quarter Q2 and accounting period 4,5 under recent ,it shows me quarter 1 , accounting period 1,2 under previous header.
oh ~ I got you see this
btw , if you don't select anything ,"pervious completed quarter" will show Q1+Q2+Q3 data
Hi Lau,
I am also having some trouble putting in condition for displaying year-2016 Quarter 4 data in previous when user selects fiscal year-2017 and quarter 1. Can you tell me where I have to put the if condition ?
Thanks in advance. Appreciate your help and patience on this
Well , I see
this will be quite different from what we did, I think we'd better figure it out relying on a better data set
so plz attach your data source file which named "FY16.xlsx" and "FY17.xlsx" in my view
Hi Lau,
Got it after trying certain possibilities , simple if condition outside our measure made it to work
maybe this:
=sum( {< [counting period] = {">=$((=min([counting period])-( GetSelectedCount([counting period])-1 ) ))<=$(=min([counting period]))"} >} revenue )