Datesbetween or Calculate after import from Excel to PBI desktop doesn't work

[miroslav_umlauf]

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))
                                     )
)

[ALeef · Accepted 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.

[Greg_Deckler]

The semi-colons do not look correct, shouldn't they be commas or is there a language pack involved.