Previous month value - wrong metadata

Question

Hi All,&nbsp;initial dataset is as in the upper table - Client has a certain Sale in each month, from Jan till September - belongs to Hong-Kong office, later office gets changes to London. How to write previous month sale measure, so that we could avoid what is happening in the lower table? For September, addition (artificial) row gets created with the prev mont Sale (becaue prev mont sale was in Hong-Kong), but this behaviour is really outrageus. I am interested in seeing prev mont sale, but within current month dimensionality. If I'm not mistaken, in multidimensional cube, this would never happen.measure def:&nbsp;Sales_PrevMonth = CALCULATE ( SUM ( 'Fact'[Sales] ), PREVIOUSMONTH ( 'Dimension Date'[Date] ) )&nbsp;&nbsp;&nbsp;

ashish_mathur · Answer

Hi,
You may download my PBI file from here.
Hope this helps.

ashish_mathur · Answer

Hi,
Share data in a format that can be pasted in an MS Excel file.&nbsp; Show the expected result in a Table format.

sk86 · Answer

Hi,&nbsp;here is a datasetDate:DateCalendar Month NumberCalendar Year2016-01-01120162016-02-01220162016-03-01320162016-04-01420162016-05-01520162016-06-01620162016-07-01720162016-08-01820162016-09-01920162016-10-011020162016-11-011120162016-12-01122016&nbsp;Office:OfficeIDOfficeName1Hong-Kong2London3Warsaw4Barcelona5Delhi&nbsp;Client:&nbsp;ClientIDClientName1Client A2Client B3Client C4Client D&nbsp;Fact:&nbsp;ClientIDDateIDOfficeIDSales12016-01-0111012016-02-0112012016-03-0113412016-04-01154612016-05-0114312016-06-01132412016-07-0116512016-08-0114512016-09-012712016-10-012612016-11-012332412016-12-01254&nbsp;&nbsp;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):&nbsp;&nbsp;YearCalendar Month NumberClientNameOfficeNameSum of SalesSales_PrevMonth20161Client AHong-Kong10&nbsp;20162Client AHong-Kong201020163Client AHong-Kong342020164Client AHong-Kong5463420165Client AHong-Kong4354620166Client AHong-Kong3244320167Client AHong-Kong6532420168Client AHong-Kong456520169Client ALondon745201610Client ALondon67201611Client ALondon33246201612Client ALondon543324&nbsp;&nbsp;&nbsp;

sk86 · Answer

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.&nbsp;We can assume that prevmont_sale can be aggregated by any dimension apart Date and Client, but associated with current month's dimensionality.&nbsp;

sk_86 · Answer

Hi Ashish,&nbsp;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 -&nbsp;REMOVEFILTERS(), KEEPFILTERS(Dates), KEEPFILTERS (Clients) ?

Forum Discussion

Previous month value - wrong metadata

6 Replies

Related content

Current Month , Previous Month and Before Previous Month

Dataflow metadata

Count previous months

Current Month vs Previous Month

Previous Month's MTD calculation

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

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

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