Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Date | POS | Sales |
01.06.2020 | 100 | 500 € |
01.06.2020 | 200 | 300 € |
01.06.2020 | 900 | 50 € |
02.06.2020 | 100 | 400 € |
02.06.2020 | 200 | 200 € |
02.06.2020 | 900 | 30 € |
03.06.2020 | 100 | 500 € |
03.06.2020 | 200 | 300 € |
03.06.2020 | 900 | 50 € |
LinkTable
POS | Sattelite | Begin | End |
100 | 900 | 01.01.2020 | 02.06.2020 |
200 | 900 | 03.06.2020 | 31.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
Date | POS | summarized Sales |
01.06.2020 | 100 | 550 € |
01.06.2020 | 200 | 300 € |
01.06.2020 | 900 | 50 € |
02.06.2020 | 100 | 430 € |
02.06.2020 | 200 | 200 € |
02.06.2020 | 900 | 30 € |
03.06.2020 | 100 | 500 € |
03.06.2020 | 200 | 350 € |
03.06.2020 | 900 | 50 € |
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
Solved! Go to Solution.
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.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |