How to filter content of one column in the context of combination of other columns including date

[NV89]

Hi,

I am very new to PowerBI and this is 1st time I am posting a question so, please pardon if something is incorrect. Here is my problem statement.

I have following table with date (random), Store, Person, Product & Sales columns. I want to create a measure which works in context of Date, Store & Person in combine and generates a resulting Output column as shown below. Output column is based on the filter of Product column based on another Table which contains list of products to use for filter. 

Table to filter:

Cereal

Tissues

Detergents

Input Table (with Output column as expected resulting column)

Date	Store	Person	Product	Sales	Output
1/1/2022	Store1	Mike	Cereal	10	10	<- Since Mike from Store1 sold an item from a given table (i.e. Cereal), output is equal to Sales column for that product
1/1/2022	Store1	Mike	Cheeses	2
1/1/2022	Store1	Mike	Crackers	1
1/1/2022	Store1	John	Tissues	5	5	<- Since John from Store1 sold an item from a given table (i.e. Tissues), output is equal to Sales column for that product
1/1/2022	Store1	John	Cheeses	15
1/1/2022	Store2	Rod	Juices	2	0	<- Since Rod from Store2 didn't sell any items from a given table and we don't have any records of him selling any item from a given table from any previous dates, resulting output is 0
1/5/2022	Store1	Mike	Juices	5	10	<- Since Mike from Store1 didn't sell any item from a given table, carry forward the sales of an item that he sold from a table on previous date i.e. he sold 10 Cereal on 1/1/2022
1/8/2022	Store1	Mike	Detergents	20	20	<- Since Mike from Store1 sold one of the item from a given table, the resulting output is 20
1/8/2022	Store2	John	Towels	10	0	<- Here, John is a different person who works in Store2 and since he didn't sell any item from a given table on current date or any of the previous dates, resulting output is 0

[v-zhangti · Accepted solution]

Hi, @NV89 

You can try the following methods.

Column:

Column = 
IF([Product] in VALUES('Filter'[Filter]),[Sales])

Output = 
Var _lastdate=
CALCULATE(MIN('Table'[Date]),FILTER('Table',[Person]=EARLIER('Table'[Person])&&[Column]<>BLANK()))
Var _InFliter=
CALCULATE(MAX('Table'[Column]),FILTER('Table',[Person]=EARLIER('Table'[Person])&&[Date]=EARLIER('Table'[Date])))
Var _lastsales=
CALCULATE(MAX('Table'[Sales]),FILTER('Table',[Date]=_lastdate
                               &&[Person]=EARLIER('Table'[Person])&&[Product] in VALUES('Filter'[Filter])))
return
IF(_InFliter=BLANK(),0,IF([Column]<>BLANK(),[Column],IF([Date]<>_lastdate,_lastsales)))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lukiz84]

Hi,

just create a measure 

Output = SUM('Input Table'[Sales])

and connect your Product table (1:n) to your Input Table (field Product). Add a slicer from the product table and voilá 🙂

(You should also create a seperate date table and connect the date field to the date field of the Input table to be able to use time intelligence functions)

br

[NV89]

This doesn't work. My problem statement is complex than what you have interpreted and replied.