Forum Discussion
Dynamic date filter based on slicer selection
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] )
)
1 Reply
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.
