Forum Discussion
Previous month value - wrong metadata
Hi,
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 |
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) ?Hi,
You are welcome. Try the ALLEXCEPT() function.
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.
