Forum Discussion
Previous month value - wrong metadata
Super UserHi,
Share data in a format that can be pasted in an MS Excel file. Show the expected result in a Table format.
Hi,
here is a dataset
Date:
Date Calendar Month Number Calendar Year 2016-01-01 1 2016 2016-02-01 2 2016 2016-03-01 3 2016 2016-04-01 4 2016 2016-05-01 5 2016 2016-06-01 6 2016 2016-07-01 7 2016 2016-08-01 8 2016 2016-09-01 9 2016 2016-10-01 10 2016 2016-11-01 11 2016 2016-12-01 12 2016 Office:
OfficeID OfficeName 1 Hong-Kong 2 London 3 Warsaw 4 Barcelona 5 Delhi Client:
ClientID ClientName 1 Client A 2 Client B 3 Client C 4 Client D Fact:
ClientID DateID OfficeID Sales 1 2016-01-01 1 10 1 2016-02-01 1 20 1 2016-03-01 1 34 1 2016-04-01 1 546 1 2016-05-01 1 43 1 2016-06-01 1 324 1 2016-07-01 1 65 1 2016-08-01 1 45 1 2016-09-01 2 7 1 2016-10-01 2 6 1 2016-11-01 2 3324 1 2016-12-01 2 54 Expected result (just one row for month 9, with both sales and prev_mont_sales in the same row, with the Office, which exists in Fact, which is London, not Hong Kong):
Year Calendar Month Number ClientName OfficeName Sum of Sales Sales_PrevMonth 2016 1 Client A Hong-Kong 10 2016 2 Client A Hong-Kong 20 10 2016 3 Client A Hong-Kong 34 20 2016 4 Client A Hong-Kong 546 34 2016 5 Client A Hong-Kong 43 546 2016 6 Client A Hong-Kong 324 43 2016 7 Client A Hong-Kong 65 324 2016 8 Client A Hong-Kong 45 65 2016 9 Client A London 7 45 2016 10 Client A London 6 7 2016 11 Client A London 3324 6 2016 12 Client A London 54 3324 - Ashish_Mathur
Hi Ashish,
thank you, this looks great. But how to make part with ",all(Office)" more generic? So let's say basically we should remove any filter on every possible table APART from Date and Client dimensions.
sth like this should work - REMOVEFILTERS(), KEEPFILTERS(Dates), KEEPFILTERS (Clients) ?
Of course we have to assume, that there can be many more dimensions (it's just Office in this example), and measure should behave the same way against all of them.
We can assume that prevmont_sale can be aggregated by any dimension apart Date and Client, but associated with current month's dimensionality.
