Cannot create link table

[sadath]

Hi
cannot create a link table,
my problem is mentioned in the report sheet.

excel file by sadathpm - 4shared

expecting anyone's help

 

[sadath]

Hi
anyone got the answer ??

 

[SimonNU]

I have trouble accessing the Excel file from my computer. Perhaps that's why no one has helped. Can you describe your problem on this thread?

 

[sadath]

Hi,

detail is as below

StoreList table has StoreNo Name Province City
(this table has around 450 stores info)

SalesData table has StoreNo Date SalesTY SalesLY Month
(450 stores sales information on day wise
Month is calculated column =format([DATE],"mmm")

Target table has StoreNo Date Target
(450 store target mothly, ie date 1/1/2015 for jan target, date 1/2/2015 for Feb target so on..)

Relationship [SalesData]Storeno with [StoreList]StoreNo
[Target]StoreNo with [StoreList]StoreNo



Pivot Table has
Province & City on Row
Sum of SalesLY , Sum of SalesTY & Target on Values
Month on Filter

My issue : when i select Month from filter SalesLY & SalesTY shows correct value but Target is not changing.
i tried to make Relationship [SalesData]Date with [Target]Date but I cant


valuable advise expected..

 

[SimonNU]

Make a date table, i.e. a continuous list of dates with no duplicates. The minimum date should be the lowest date in your entire data model and the maximum should be the maximum in your entire data model.

Connect SalesData[Date] and Target[Date] to DateTable[Date]. Add DateTable[Date] to the filter.

 

[sadath]

Hi
thank you for the response,

Tried your suggestion, still is the target is showing wrong (all the cities showing same target). reason may be there is no relation [StoreList] and [DateTable]

I need the 'Month' to the filter, reason is the report is a monthly report not a daywise report.

Eg: assume 20 days sales are updated in Jan month.
Then if i select Jan from the filter the report should be

CityName SalesLY SalesTY Target

(sales detail will be 1 to 20th sales total & target for Jan)

hope my problem is clear now.....

 

[SimonNU]

Please click here to download a working model of what you described.

It's an Excel 2013 data model. If you can't view it in Excel (due to having Excel 2010, perhaps) then you can convert it using PowerBI's import wizard to view the data model.

 

[sadath]

hi,

i was not related [Target]StoreNo and [StoreList]Store.

solution got from your file, thanks a lot.