CODEWITHSUNDEEP
datepowerbidaxpowerbi-desktop
BrNyh
BrNyh

Reputation: 9

Power BI: Filter sales table with multiple locations on respective start dates from different table

I've tried to find a similiar thread on this, but have not been able to do so. Im pretty new to Power BI, so i might not know what im looking for. I could really use some advise.

I have a sales table ('SalesTable') that contains all the sales from different store locations. The table includes all the sales from each store beginning in january 2021, but the stores was incorporated on different dates in 2021, and so i need to be able to make a filter to only return the sales for each store from when the stores was incorporated respectivaly.

Simplified, the tables looks like this:

'SalesTable' 'SalesTable'

'Stores' 'Stores'

The two tables are joined on storeID. SalesTable is also connected to a dax-created Calender table. The stores table is not connected to the calender table (Maybe it should??).

I need to be able to filter the report so that it only returns sales dated on or after the respective incorporateddate.

Like this: 'Desired output'

I am not sure whats the optimal way to go about this. If i should make a calculated table of the SalesTable, or if a measure is sufficient to filter the report. Any suggestions, tips or solutions would be highly appreciated :)

Upvotes: 0

Views: 151

Answers (1)

msta42a
msta42a

Reputation: 3741

You can use this measure:

sumIncorp = 
var __maxIncorp = CALCULATE(max(inc[IncorporatedDate]), FILTER(inc, inc[StoreID] = SELECTEDVALUE(IncSale[StoreID])))

return

CALCULATE(SUM(IncSale[Amount]), FILTER(IncSale, IncSale[Date] >= __maxIncorp))

enter image description here

Upvotes: 0

Related Questions