topic Re: Transaction table - first date and last date a "value" set was present in DAX Commands and Tips

Transaction table - first date and last date a "value" set was present

kj30328 — Wed, 23 Sep 2020 04:13:33 GMT

Hello,

I am looking for a way to display the first and last date a store may have sold a specific product. Illustrative dataset as follows:

Date,Region,Store_City,Transaction_Type,Store_id,Product_Code
20-Sep-20,NA,Atlanta,Retail,30328-114,200129
20-Sep-20,NA,Atlanta,Retail,30328-114,200129
20-Aug-20,NA,Atlanta,Retail,30328-114,200129
20-Aug-20,NA,Atlanta,Retail,30328-114,200129
20-Jul-20,NA,Atlanta,Retail,30328-114,30000481
20-Jul-20,NA,Atlanta,Retail,30328-114,30000481
20-Jul-20,NA,Atlanta,Retail,30328-114,200129
20-Jul-20,NA,Atlanta,Retail,30328-114,30002020
20-Jul-20,NA,Atlanta,Retail,30328-114,200129
20-Jul-20,NA,Atlanta,Retail,30328-114,30002020
20-Jul-20,NA,Atlanta,Retail,30328-114,30000757
20-Jul-20,NA,Atlanta,Retail,30328-114,30000762
20-Jul-20,NA,Atlanta,Retail,30328-114,30004019
20-Jul-20,NA,Alpharetta,Retail,30004-114,30000766
20-Jun-20,NA,Alpharetta,Retail,30004-114,30002020
20-Jun-20,NA,Alpharetta,Retail,30004-114,30002020
20-Jun-20,NA,Alpharetta,Retail,30004-114,200129
20-Jun-20,NA,Alpharetta,Retail,30004-114,200129
20-Jun-20,NA,Alpharetta,Retail,30004-114,30004019
20-Jun-20,NA,Alpharetta,Retail,30004-114,30000757
20-Jun-20,NA,Alpharetta,Retail,30004-114,30000766
20-Jun-20,NA,Alpharetta,Retail,30004-114,30000481
20-Jun-20,NA,Alpharetta,Retail,30004-114,30000481
20-Jun-20,NA,Alpharetta,Retail,30004-114,30000762
20-May-20,NA,Alpharetta,Retail,30004-114,200129

Tried to extrapolate examples from other responses but no luck, any assistance is appreciated,

Thanks

Re: Transaction table - first date and last date a "value" set was present

Greg_Deckler — Wed, 23 Sep 2020 04:47:12 GMT

kj30328 Sorry, having trouble following, can you post sample data as text and expected output?

Maybe: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Transaction table - first date and last date a "value" set was present

kj30328 — Thu, 24 Sep 2020 07:24:45 GMT

I am sorry if this is a duplicate...can't find my previous reply.....The the result I need to get to are the first and last day a product may have sold in a store...after proecssing the results would be:

20-Sep-20,NA,Atlanta,Retail,30328-114,200129
20-Jul-20,NA,Atlanta,Retail,30328-114,200129

20-Jul-20,NA,Atlanta,Retail,30328-114,30000481

20-Jul-20,NA,Atlanta,Retail,30328-114,30002020

20-Jul-20,NA,Atlanta,Retail,30328-114,30000757

20-Jul-20,NA,Atlanta,Retail,30328-114,30000762

20-Jul-20,NA,Atlanta,Retail,30328-114,30004019

------new store-----
20-Jul-20,NA,Alpharetta,Retail,30004-114,30000766
20-Jun-20,NA,Alpharetta,Retail,30004-114,30000766

20-Jun-20,NA,Alpharetta,Retail,30004-114,200129
20-May-20,NA,Alpharetta,Retail,30004-114,200129

20-Jun-20,NA,Alpharetta,Retail,30004-114,30004019

20-Jun-20,NA,Alpharetta,Retail,30004-114,30000757

20-Jun-20,NA,Alpharetta,Retail,30004-114,30002020

20-Jun-20,NA,Alpharetta,Retail,30004-114,30000481

20-Jun-20,NA,Alpharetta,Retail,30004-114,30000762

Re: Transaction table - first date and last date a "value" set was present

Anonymous — Thu, 24 Sep 2020 08:41:23 GMT

Looks like this should be done in Power Query. Take a look at PQ: https://youtu.be/ioVM77JDIXE

Re: Transaction table - first date and last date a "value" set was present

kj30328 — Fri, 25 Sep 2020 12:39:57 GMT

Hello,

Just a quick note...I wa able to accomplish what I was looking for by using transformatio and the group by function (Region, Store_city, Transaciton_type ) with resulting Max/Min dates.

Thank you all for your help and suggestions