Filtering logic Issue

[Aazamk]

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)
        )
)

[Anonymous · Accepted solution]

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.