dimension filter dax problem

[jameshug]

I try to do the model something problem.
My problem and example is
Sales fact table <-dimension times
Sales fact table <-dimension company
Sales fact table <-employee
Dimension table had date time and company.
Dim date
Company_id,datekey , working days,monthlyid
1, 2013/1/1, 20,201301
1,2013/2//1,19 ,201302
2,2013/1/1, 21 ,201301
3, 2013/1/1, 22 ,201301
.
.
.
The table have all date between 2012/1/1 to 2013/12/31 by all company and all dimension table have company_id.
This is my repot Sample
filter company id =1
Date , amount ,total amount , working days
2013/1/3 ,200 , 550 ,20
2013/1/4 ,200 , 550 ,20
2013/1/7, 100 , 550 ,20
2013/1/8, 90 , 550 ,20
2013/1/9, 10 , 550 ,20
2013/1/10, 100, 550 ,20
2013/1/11, 50 , 550 ,20
.
.
.
.
Show the All 2013/1 data
I had three slice .
Year -->2012,2013
month --> 1 to 12
company --> 1 , 2, 3

My report problem is the total amount. If I filters to company , that total amount got wrong data.
For example:
company id =2
2013/1 total : 300
this company 2013/1/2 not holiday
Ther report total will be show only have the date data company_id total, not all company_id total.
Date , amount ,total amount , working days
2013/1/2, 100, 300 ,21
2013/1/3 ,200 , 850 ,20
2013/1/4 ,200 , 850 ,20
I writed the DAX
[total amount]:=if(HASONEFILTER('Dim date'[monthlyid]),
CALCULATE(sumx('Dim date',[sale amount]),
filter(all('Dim date'),
filter(VALUES('Dim date'[COMPANY_id]),'Dim date'[COMPANY_ID]=
EARLIER('Dim date'[COMPANY_ID])&&'Dim date'[Datekey]=EARLIER('Dim date'[Datekey]))),
SUMMARIZE('Dim date','Dim date'[MONTHLYID],'Dim date'[COMPANY_ID])),[sales total])

I don't know how to fix it. I am very confused this problem. If you have any idea , please kindly help me .

Best regards.
James.

 

[Jacob Barnett]

James,

That's a pretty complex formula and its difficult to work out what's going on without a fuller description of your model and some more sample data.

My suspicion is that your formula is an over complication of what's really required, partly driven perhaps by an unusual data structure - its generally accepted that the date table should have a single column of unique, contiguous dates. It seems likely that your use of a separate date for each company combined with the ALL('DimDate') in your initial FILTER() is what is causing the issue. The triple iteration could also be an issue.

If you post back with some more details on the model structure and the nature of the data then I'd be happy to help further, better still a sample model uploaded to Google Drive/DropBox/SkyDrive.

Jacob