Dynamic date filter based on slicer selection

PKPK90 — Thu, 17 Aug 2023 08:58:09 GMT

Hi, what I want to change in my code is to use slicer selected value to filter OrderDate, right now I have date function which is hard coded.

I have created Calendar table which can be useful but still do not know how to use it in correct way.

My Measure:

WeightedAverageCost =

VAR tabl1 =

CALCULATETABLE (

ADDCOLUMNS (

SUMMARIZE ( PurchaseOrders, PurchaseOrders[Site_Item_Key]),

"ItemExtendedCost", [M_ExtendedCost]

FILTER(PurchaseOrders,PurchaseOrders[OrderDate]>DATE(2022,08,01)), <- change to slicer value

ALLSELECTED ( Items )

)

VAR tabl2 =

SELECTCOLUMNS (

FILTER(

PurchaseOrders,PurchaseOrders[OrderDate]>DATE(2022,08,01)), <- change to slicer value

PurchaseOrders[Site_Item_Key],

PurchaseOrders[Site],

PurchaseOrders[Item],

PurchaseOrders[OrderDate],

PurchaseOrders[PurchaseOrder],

PurchaseOrders[PurchaseOrderLine],

PurchaseOrders[ItemCost],

PurchaseOrders[ExtendedCost]

)

VAR tabl3 =

NATURALINNERJOIN ( tabl1, tabl2 )

VAR tabl4 =

ADDCOLUMNS (

tabl3,

"%ofTotal", FORMAT ( DIVIDE ( [ExtendedCost], [ItemExtendedCost] ), "Percent" ),

"WeightedItemCost", DIVIDE ( [ExtendedCost], [ItemExtendedCost] ) * PurchaseOrders[ItemCost]

)

RETURN

GROUPBY (

tabl4,

[Site],

[Item],

[Site_Item_Key],

"TotalWeightedItemCost", SUMX ( CURRENTGROUP (), [WeightedItemCost] )

)

Re: Dynamic date filter based on slicer selection

Prateek97 — Thu, 17 Aug 2023 09:02:17 GMT

Hi,

Instead of 'FILTER(PurchaseOrders,PurchaseOrders[OrderDate]>DATE(2022,08,01))', you can try writing
FILTER(PurchaseOrders,PurchaseOrders[OrderDate]>SELECTEDVALUE(Calendar[Date])).

Please mark as answer if works.

topic Dynamic date filter based on slicer selection in DAX Commands and Tips

Dynamic date filter based on slicer selection

Re: Dynamic date filter based on slicer selection