Forum Discussion
Dynamic calculation based on date slicer
just add the region filter. (I messed with your sample data as it only had West region)
Super UserHave you used PRODUCTX() before? It sounds like the perfect candidate for what you are trying to achieve.
"Custom" is a Date column that is defined in Power Query and is used for date sorting.
#"Added Custom" = Table.AddColumn(Source, "Custom", each "1-" & [Date]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}, {"Value", type number}})
You can apply date range filters, replace the SELECTEDVALUE() with MAX(), change the ALLSELECTED() to ALLEXCEPT() etc. depending on your needs.
Thank you for your response. I tried the formula, it does work but I also need the ability to filter by the Postal Code. The Postal code is in a separate table with the region. I have a code table for the region which links to both the original table and the table with Postal Code. As soon as I add the Postal Code as a filter, the measure no longer works, it calculates the wrong value. The Postal Code is not part of the original table.
| region | postal code |
| west | A1B 2C3 |
| west | D4E 5F6 |
| east | G7H 8I9 |