Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Hi,
Hope you all had a great thanksgiving. I have a calender table and Orders table. I'm trying to create a measure to give the count of orders for the last 364 days, meaning, need order count from 12/18/2019 to 12/15/2020 and I don't want to consider today. My measure follows,
LateOrders = CALCULATE ( COUNT ('Table'[OrderId]), DATESINPERIOD('DimDate'[Date] , LASTDATE(DimDate[Date]), -364, DAY ))
When I use this measure, it's showing me the same count as that of what I got for date range from 12/18/2019 to 12/16/2020. Not sure how to alter the formula not to consider today for the order count. Any ideas ?
Thanks in advance.
Solved! Go to Solution.
Please try this expression
LateOrders =
VAR vToday =
TODAY ()
RETURN
CALCULATE (
COUNT ( 'Table'[OrderId] ),
FILTER (
ALL ( 'DimDate'[Date] ),
'DimDate'[Date] < vToday
&& 'DimDate'[Date] >= vToday - 365
)
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Please try this expression
LateOrders =
VAR vToday =
TODAY ()
RETURN
CALCULATE (
COUNT ( 'Table'[OrderId] ),
FILTER (
ALL ( 'DimDate'[Date] ),
'DimDate'[Date] < vToday
&& 'DimDate'[Date] >= vToday - 365
)
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.