Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
We have used the below expression to get the resuts as per business rules.
We got a requirement that we need to show the results roll banck to 6 months.
Plase help me how to get the results.
Logic used : = IF(isnull(num(sum({$<[Month Abbr]=, Year=, [Quarter Name]=, [Calendar Date]={'$(=$(TestDate))'}>}[LS Open]),'#,##0')),
Null(),
Num( num(sum({$<[Month Abbr]=, Year=, [Quarter Name]=, [Calendar Date]={'$(=$(TestDate))'}, [LS Age ]={1}>} [LS Open]),'#,##0')
/
num(sum({$<[Month Abbr]=, Year=, [Quarter Name]=, [Calendar Date]={'$(=$(TestDate))'}>}[LS Open]),'#,##0'),'#,###0.0%'))
where $(TestDate) = Date(Addmonths(max({$<[# QN Open]={1}>}[Calendar Date]), -1, 1), 'DD-MMM-YYYY')
Thanks..
Hi,
to be able to rollback using set analysis, yo have two options:
1. use Month_year or Year_month field
2. use Date field
in your actual expression, you're detaching Year and Month fields from the expression and you're setting a specific date.
So, to be able to roll back, I think you can use your date field, but remove that variable and replace it with something like:
[Calendar Date]={'>$(=AddMonths(max([Calendar Date]),-6))<=$(=max([Calendar Date]))'}
Hi nareshthavidishetty ,
Please use below code and convert your expression like below.
Sum({<DateField={">=$(=addmonths(max(DateField),-5))<=$(=max(DateField))"},Year=,Month=,Quarter=>}Sales)
Thanks,
Arvind Patil
Hi,
Thanks for the inputs..It's working fine.
But i need to display the in between periods as well like columns july 2017,aug 2017.....
With the approaches as suggeted in earlier,it will show only 6th month.ut ineed tomshow in between months as well.
Rules : Table straight table.Already having another dimension.
Thanks..