Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I would like to have the sum of last 12 month based on the maximum "Invoice_date" done on the selection.
How this can be build?
This is the formula for sales based on the selection (imagine May - 2017)
=Sum(ExtendedUSD)
Which shuold be the formula to retrieve the last 12 month sales ?
Do you have MonthYear field as a dimension? Is it pivoted? Essentially going down or going across?
Yes i have it. MonthYear is on my data model.
If Jan17 is selected, i would like to have sum (Jan 16 -> Dec 16 )
Is it in the chart also?
no only on a pivot table
That is what I meant... do you mind sharing a sample to help you better?
Find attached.
Let's say that i would like to have rolling of previous 3 month based on the month selected (.e.g November 2017)
Thansk
Try this expression
=if(vExcludeInstrumentSales,
Sum({<MonthYear = {"$(='>=' & Date(AddMonths(Max(MonthYear), -2), 'MMM-YYYY') & '<=' & Date(Max(MonthYear), 'MMM-YYYY'))"}, Month, Year>}ExtendedUSD),
Sum({<Item_Category={$(vNoInstruments)}, MonthYear = {"$(='>=' & Date(AddMonths(Max(MonthYear), -2), 'MMM-YYYY') & '<=' & Date(Max(MonthYear), 'MMM-YYYY'))"}, Month, Year>}ExtendedUSD))
it works!
btw, as you can see below, if i select MONTH as dimension on top, the rolling is then under each month.
There's a way to have Sales as value under Month, and then on the right just one column with the Rolling Sales ?
THanks!
Sales
=if(vExcludeInstrumentSales,
Sum({<MonthYear = {"$(='>=' & Date(AddMonths(Max(MonthYear), -2), 'MMM-YYYY') & '<=' & Date(Max(MonthYear), 'MMM-YYYY'))"}, Month, Year>}ExtendedUSD),
Sum({<Item_Category={$(vNoInstruments)}, MonthYear = {"$(='>=' & Date(AddMonths(Max(MonthYear), -2), 'MMM-YYYY') & '<=' & Date(Max(MonthYear), 'MMM-YYYY'))"}, Month, Year>}ExtendedUSD))
Rolling Sales 12M
=RangeSum(Before(if(vExcludeInstrumentSales,
Sum({<MonthYear = {"$(='>=' & Date(AddMonths(Max(MonthYear), -2), 'MMM-YYYY') & '<=' & Date(Max(MonthYear), 'MMM-YYYY'))"}, Month, Year>}ExtendedUSD),
Sum({<Item_Category={$(vNoInstruments)}, MonthYear = {"$(='>=' & Date(AddMonths(Max(MonthYear), -2), 'MMM-YYYY') & '<=' & Date(Max(MonthYear), 'MMM-YYYY'))"}, Month, Year>}ExtendedUSD)), 0, 3))