Filtering logic Issue

Aazamk — Tue, 22 Aug 2023 11:02:12 GMT

I have multiple transactions on the same date but each date has a different item cost, In my report, I want the latest item cost which can be differentiated by modified_date in my table. So I am not good in DAXs, I am trying to figure out how to filter the transaction date. I tried but it was not showing values on my selected date range. Below is my DAX and Script.

in this, you can see I have modified_date with hours and minutes from which I want to filter out

CREATE OR REPLACE VIEW lookup_table AS SELECT a.transaction_id,b.transaction_detail_id,c.sku_id,d.item_id,e.category_id,f.location_id,a.modified_date, a.transaction_date,(b.item_cost) AS "Item Cost", SUM(b.quantity) AS 'Item On Hand', SUM(b.quantity)*SUM(b.item_cost) AS 'Total Cost', SUM((b.quantity) * (b.item_cost)) AS 'Extended Cost', SUM((b.quantity)* (d.worth)) AS 'Extended Worth', SUM(CASE WHEN a.ref_type = 1 THEN b.quantity ELSE 0 END) AS 'Item Sold', ABS(CASE WHEN a.ref_type = 1 THEN b.quantity ELSE 0 END) AS 'Item Sold(ABS)', SUM(CASE WHEN a.ref_type <> 1 THEN b.quantity ELSE 0 END) AS 'Remaining Item', (b.item_cost*SUM(CASE WHEN a.ref_type <> 1 THEN b.quantity ELSE 0 END)) AS 'Remaining Item Cost' FROM inv_transactions a JOIN inv_transaction_details b ON a.transaction_id = b.transaction_id JOIN inv_skus c ON c.sku_id = b.sku_id JOIN inv_items d ON d.item_id = c.item_id JOIN inv_categories e ON e.category_id = d.category_id JOIN inv_locations f ON f.location_id = a.location_id #WHERE d.item_code = "P14" #and d.item_id =1430 #where a.transaction_id = 70 #and year(a.transaction_date) = 2022 #and f.location_id = 4 GROUP BY a.transaction_id,b.transaction_detail_id,c.sku_id,d.item_id,e.category_id,f.location_id, a.transaction_date,a.modified_date

item cost for max date = SUMX ( SUMMARIZE ( 'Lookup Table', 'INV Sku'[sku], 'INV Categories'[name], 'INV Items'[item_code], 'INV Locations'[location_name] ), VAR MaxDate = CALCULATE ( MAXX('Lookup Table', 'Lookup Table'[transaction_date]), ALL ( 'Lookup Table'[transaction_date] ) ) RETURN CALCULATE ( MAX( 'Lookup Table'[Item Cost] ), KEEPFILTERS ( 'Lookup Table'[transaction_date] = MaxDate) ) )

Re: Filtering logic Issue

Anonymous — Thu, 24 Aug 2023 02:02:51 GMT

Hi Aazamk ,

According to your description, you want to filter transaction date based on date slicer, right? Here are my steps you can follow as a solution.

(1)Create a date table with date field as slicer.

Date = CALENDAR(DATE(2022,1,1),DATE(2023,12,31))

(2)Create a measure.

Flag = IF(MAX('Lookup Table'[transaction_date]) <= LASTDATE('Date'[Date]) && MAX('Lookup Table'[transaction_date]) >= FIRSTDATE('Date'[Date]),1,0)

(3)Place [Flag]=1 on the visual object filter.

Best Regards,

Neeko Tang

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

topic Re: Filtering logic Issue in DAX Commands and Tips

Filtering logic Issue

Re: Filtering logic Issue