getting nowhere with DATESBETWEEN - invalid numeric representation of a date value was encountered

[RichardBarnard]

Hi, I am really stuck with this (writing measure only within PowerPivot model).. but keeping things very simple, I have a calendar table and then some sales data. the tables are linked by date relationship. calendar table is set as the date table.

My calendar dates are in format (UK) so 01/01/2025 for example. 

My ultimate objective is to be able to pull sales for a set date range using DATESBETWEEN...

Before I even get that far, I can't even derive a pivot table of dates to verify DATESBETWEEN even works. My measure for this is simply: =DATESBETWEEN('Calendar'[Date],01/01/2025,31/01/2025) . Category set to Date and the short date format selected.
When pivoting I would hope to reveal the list of dates but receive this error:

"MdxScript(Model) (6, 46) Calculation error in measure <Measure name>: An invalid numeric representation of a date was encountered. 

Note the measure can't be dragged into rows- i'm assuming this is normal?

Is this DAX perhanps not available outside of power BI?

Any other suggestions to overcome this? I have determined start and end dates.. i just want to sum my sales based on those.. 

Thanks!!

[v-hashadapu]

Hi @RichardBarnard , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.

[RichardBarnard]

thanks all for the support!! and prompt engagement. I am still trying to get back to this piece of work so I can test out your proposals. Will update and mark solutions asap. thank you!

[v-hashadapu]

Hi @RichardBarnard , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[FBergamaschi]

Hi @RichardBarnard ,

@johnt75 gave you already some great suggestions, I want to add to his valuable contribution the answer to your question

Note the measure can't be dragged into rows- i'm assuming this is normal?

The answer is that this is normal, you cannot group a measure but only a column. The reason is that a measure does not have any value to inspect to generate the list of distinct values (that's what grouping means and that's what happens if you drag and drop a column into the Rows/Columns section of a pivot). On the contrary, a column has a list of values (since it is a column, so by definition) and so out of the values

Column

A

B

A

C

D

B

B

you get

A

B

C

D

But this is not possible with a measure for the reason written above.

A final detail: the above should also be valid for the filters section of pivot tables and for slicers. And indeed this is the case in Power Pivot.

BUT

In Power BI you can use a measure as a filter, a thing that should be impossible based on what I wrote above. Point is that Micrsoft defines only in Power BI a sophsticated mechanism to allows you to use a measure as a filter on a visual. The post would become terribly long if I went into details, but feel free to open another post in which you might want us to get deep into this fantastic (but complex to understand) feature in the future!

Hope this adds some value to what @johnt75 wrote

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[johnt75]

There's a couple of issues here. Firstly, to specify date arguments you need to use the DATE function,  so it would be

DATESBETWEEN('Calendar'[Date], DATE( 2025, 1, 1 ) DATE( 2025, 1,31 ) )

Secondly, DATESBETWEEN returns a table whereas a measure needs to return a scalar value. For checking you could wrap COUNTROWS around the DATESBETWEEN.

[RichardBarnard]

Thank you. I will take a look and see if I can get somewhere with this. as I progress this, I actually want to define "START DATE" "END DATE" so my formulae   for DATE(... ) would just reference those directly. I need to do some reading  up around that scalar vs table thing. My experience is quite limited as you can tell! Thanks