Forum Discussion
Create a Date Slicer to Influence Calculated Measures
Hi finnprice
It’s important to clarify to the head of ERP that certain functionalities are essential and should be used as intended. Field parameters are designed to switch between dimensions/measures, not to combine or compare them in the way you're trying to achieve.
For example, in your table, Row 1 has an Order Date of February 1 and a Shipment Date of February 2. If you use a field parameter to switch the filter to Shipment Date, the row will still be included—because the Shipment Date is February 2—even though the Order Date is February 1. This means the filter only respects the selected dimension (Shipment Date in this case), and not the Order Date.
Below is a workaround
completed based on created date = CALCULATE ( SUM ( Data[Value] ), FILTER ( ALL ( Data ), Data[Completed Date] IN VALUES ( Data[Created Date] ) ) )In the above formula, the Created Date has been added to the visual, and it currently returns rows where the Completed Date matches the Created Date. However, this approach has limitations. Once additional dimensions are added to the visual, you may start seeing unexpected results.
Additionally, this setup only includes Completed Dates that also exist in the Created Date column. So, for example, if October 2 appears in the Completed Date but not in the Created Date, it will be excluded from the results.
PS: you can create dates table without importing an external data with M or DAX.
Hi danextian ,
Thanks for taking the time to help!
Your solution looks like a great option when you have a master date table, however the head of ERP does not want me to create additional tables in this workspace. Instead, he is hoping I can find a solution using parameters - do you think there is a way to create this functionality using parameters?
P.s. thank you for cleaning up my filter syntax, still learning DAX so all best-practice revisions are welcome!
Super UserHi finnprice
It’s important to clarify to the head of ERP that certain functionalities are essential and should be used as intended. Field parameters are designed to switch between dimensions/measures, not to combine or compare them in the way you're trying to achieve.
For example, in your table, Row 1 has an Order Date of February 1 and a Shipment Date of February 2. If you use a field parameter to switch the filter to Shipment Date, the row will still be included—because the Shipment Date is February 2—even though the Order Date is February 1. This means the filter only respects the selected dimension (Shipment Date in this case), and not the Order Date.
Below is a workaround
completed based on created date =
CALCULATE (
SUM ( Data[Value] ),
FILTER ( ALL ( Data ), Data[Completed Date] IN VALUES ( Data[Created Date] ) )
)
In the above formula, the Created Date has been added to the visual, and it currently returns rows where the Completed Date matches the Created Date. However, this approach has limitations. Once additional dimensions are added to the visual, you may start seeing unexpected results.
Additionally, this setup only includes Completed Dates that also exist in the Created Date column. So, for example, if October 2 appears in the Completed Date but not in the Created Date, it will be excluded from the results.
PS: you can create dates table without importing an external data with M or DAX.