The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
Hi all, already spent hours finding an error, but if it works in classic Excel PowerPivot, then after import until update, it must be a bug ... Or? Is there anything wrong the syntax of the formula bellow?
PowerBI desktop says: The syntax for 'CALCULATE' is incorrect.
The relationship between Calendar and Orders is ok.
Customers last 7 days:=CALCULATE ( [Unique customers];
DATESBETWEEN( Calendar[Date];
CALCULATE ( LASTDATE( Orders[ORDER DATE] ); ALL(Orders))-7;
CALCULATE ( LASTDATE( Orders[ORDER DATE] );ALL(Orders))
)
)
Solved! Go to Solution.
Try:
UniqueLast7 = CALCULATE(DISTINCTCOUNT(Orders[CustomerID]), DATESBETWEEN (Orders[Order Date], (NOW()-7), NOW())
Assuming that you have CustomerID in the Orders table, along with an Order Date. This should count the number of distinct customer IDs in the Orders table, between the dates 7 days ago and now.
This will include a timestamp, assuming it is a full date-time field, so if you run this at 8am on monday, an order places at 7am last monday would be excluded using the NOW function. Hopefully that helps - unless I'm missing the question, you shouldn't have to nest CALCULATEs to get the information you want.
Try:
UniqueLast7 = CALCULATE(DISTINCTCOUNT(Orders[CustomerID]), DATESBETWEEN (Orders[Order Date], (NOW()-7), NOW())
Assuming that you have CustomerID in the Orders table, along with an Order Date. This should count the number of distinct customer IDs in the Orders table, between the dates 7 days ago and now.
This will include a timestamp, assuming it is a full date-time field, so if you run this at 8am on monday, an order places at 7am last monday would be excluded using the NOW function. Hopefully that helps - unless I'm missing the question, you shouldn't have to nest CALCULATEs to get the information you want.
The semi-colons do not look correct, shouldn't they be commas or is there a language pack involved.
User | Count |
---|---|
160 | |
111 | |
96 | |
86 | |
75 |
User | Count |
---|---|
158 | |
136 | |
133 | |
81 | |
61 |