Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
axel_BI
Helper I
Helper I

Relate Sattelite Sales to Shop

Hi everyone,

again im looking for your help.

 

Situation: There are shops, which generate Sales each day. Also there are sattelites, which generate sales each day. The difference is, that the sattelites receive their products from a releated shop.

Therfore it is necessary, to allocate the generated sales of a sattelite to its related shop.

 

Provided are two tables. One with the data of sales. The other one with the given relationship for each day.

 

Example:

100 and 200 are ShopID

900 is SatelliteID

 

Sales

DatePOSSales
01.06.2020100500 €
01.06.2020200300 €
01.06.202090050 €
02.06.2020100400 €
02.06.2020200200 €
02.06.202090030 €
03.06.2020100500 €
03.06.2020200300 €
03.06.202090050 €

 

LinkTable

POSSatteliteBeginEnd
10090001.01.202002.06.2020
20090003.06.202031.12.9999

 

So the sales of the sattelite till the 02.06 are related to shop 100
After that the sattelite sales belong to Shop 200

 

GoalTable

DatePOSsummarized  Sales
01.06.2020100550 €
01.06.2020200300 €
01.06.202090050 €
02.06.2020100430 €
02.06.2020200200 €
02.06.202090030 €
03.06.2020100500 €
03.06.2020200350 €
03.06.202090050 €

 

If i still get the sales of ID 900 doesn't matter. I could filter them if need to be.

 

I think the way to go would be summarize a new table, with a conditioned sum. But i cant quite figure it out.

 

Geetings and Thanks in advance

Axel

1 ACCEPTED SOLUTION
axel_BI
Helper I
Helper I

Kind of solved myself:

I created a new column, in which the relevant ID is shown. Either the ShopID or the SatteliteID, depending if there is an entry in the link-table, and if the sales-date is in the corresponding time intervall.

New ID =
Var _Old ID = Sales[POS]
Var _Date = Sales[DATE]
Return
IF(NOT(ISBLANK(CALCULATE(COUNTROWS(LinkTable),
Filter( 
Filter(
Filter( LinkTable, LinkTable[End].[Date]>_Date),
LinkTable[Begin].[Date]<=_Date ),
LinkTable[Pos]= _OldID)
))),
LOOKUPVALUE(LinkTable[Sattelite],LinkTable[POS],_OldID),_OldID)


Then i an create my sum of sales on this new column.

 

View solution in original post

1 REPLY 1
axel_BI
Helper I
Helper I

Kind of solved myself:

I created a new column, in which the relevant ID is shown. Either the ShopID or the SatteliteID, depending if there is an entry in the link-table, and if the sales-date is in the corresponding time intervall.

New ID =
Var _Old ID = Sales[POS]
Var _Date = Sales[DATE]
Return
IF(NOT(ISBLANK(CALCULATE(COUNTROWS(LinkTable),
Filter( 
Filter(
Filter( LinkTable, LinkTable[End].[Date]>_Date),
LinkTable[Begin].[Date]<=_Date ),
LinkTable[Pos]= _OldID)
))),
LOOKUPVALUE(LinkTable[Sattelite],LinkTable[POS],_OldID),_OldID)


Then i an create my sum of sales on this new column.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.