topic How to format a date calculation to give a number / calculate numbers of days based on slicer in DAX Commands and Tips

How to format a date calculation to give a number / calculate numbers of days based on slicer

Anonymous — Thu, 07 Jul 2022 08:46:06 GMT

Hello,

I am trying with this formula to create a calculation of the days based on the selection in a date slicer (so the count of actual days e.g. if the slicer is set to show the last 60 days, the measure will return the number 60, which I can then use in other calculations). However, I am stuck and nothing is working. I know there is a lot on this topic available, but I have not been able to use any of that info yet.

This is the dax measure:

DataScore_13-count_days_slicer =

VAR FirstDay = CALCULATE(

MIN(DIM_DATE_D1[Date]),

ALLSELECTED(DIM_DATE_D1[Date])

)

VAR LastDay = CALCULATE(

MAX(DIM_DATE_D1[Date]),

ALLSELECTED(DIM_DATE_D1[Date])

)

RETURN

DATESBETWEEN(DIM_DATE_D1[Date],FirstDay,LastDay)

The DIM_DATE_D1[Date] is the date table I am using (corporate date table)

What am I doing wrong?

So just to be clear, I need a measure that gives me the amount of dates, based on the date slicer settings on my report pages.

Cheers,

Carsten

Re: How to format a date calculation to give a number / calculate numbers of days based on slicer

tamerj1 — Thu, 07 Jul 2022 08:57:27 GMT

Hi Anonymous
DATESBETWEEN returns a table. My understanding that you want to return the number of selected days. You can achieve that by returning

RETURN
DATEDIFF ( FirstDay, LastDay, DAY )

Re: How to format a date calculation to give a number / calculate numbers of days based on slicer

Anonymous — Thu, 07 Jul 2022 10:52:28 GMT

Hi tamerj1

Thanks a lot, it is clear and the dateiff funtion works, brilliant. Very much appreciated. And I also inderstand datesbetween much better now.

Cheers,

Carsten