Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!See when key Fabric features will launch and what’s already live, all in one place and always up to date. Explore the new Fabric roadmap
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.
User | Count |
---|---|
91 | |
74 | |
71 | |
58 | |
55 |
User | Count |
---|---|
41 | |
39 | |
34 | |
32 | |
30 |