Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I have a matrix visual that is filtered by a relative date filter. I want to create a measure that will ignore that filter and bring in sales orders that are in the next 14 days fom today.
Here is what I have so far. But it is bringing in all the sales orders in the future.
Sales Orders After Today No Filter = CALCULATE(FactSalesOrderLines[Cases Ordered],
DATEADD(DimDate[Date],+14,DAY),
FILTER (
ALL(DimDate[Date]),
DimDate[Date] > TODAY()
)
)
Solved! Go to Solution.
Hi @jpt1228
Here is one way to do it. Just make sure you use the Date field from DimDate on your Axis and may need to remove the relationship between the two tables.
Sales Orders After Today No Filter = CALCULATE( SUM(FactSalesOrderLines[Cases Ordered]), FILTER( 'DimDate', [Date]>=TODAY() && [Date]<TODAY()+14 ) )
Hello @Phil_Seamark - I was able to get this to work in a new matrix visual but when I add the measure into the other matrix visual that has relative date filtering it doesn't work.
Here is the formula that I got to work. Since I had a page filter using the relative date filter set to "Today" This formula works to bring in the sales in the next 14 days from the page filter date. This also works to bring in sales previous to the date filter as well.
SO Cases in next 14 days =
CALCULATE(
FactSalesOrderLines[Cases Ordered],
DATESINPERIOD(DimDate[Date],
LASTDATE(DimDate[Date]),
+14, DAY
))
Thanks
Hi @jpt1228
Here is one way to do it. Just make sure you use the Date field from DimDate on your Axis and may need to remove the relationship between the two tables.
Sales Orders After Today No Filter = CALCULATE( SUM(FactSalesOrderLines[Cases Ordered]), FILTER( 'DimDate', [Date]>=TODAY() && [Date]<TODAY()+14 ) )
Hello @Phil_Seamark - I was able to get this to work in a new matrix visual but when I add the measure into the other matrix visual that has relative date filtering it doesn't work.
Here is the formula that I got to work. Since I had a page filter using the relative date filter set to "Today" This formula works to bring in the sales in the next 14 days from the page filter date. This also works to bring in sales previous to the date filter as well.
SO Cases in next 14 days =
CALCULATE(
FactSalesOrderLines[Cases Ordered],
DATESINPERIOD(DimDate[Date],
LASTDATE(DimDate[Date]),
+14, DAY
))
Thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |