Calender view slicer issue

[Vamshi2020]

Hey guys,

i need to write a measure to 
Calculate no of rows of a table after last refresh date(which is done), but on the same line i have a calender slicer, if user selects any date range, it should count rows from end date value.

Total Booked=var a= maxx(Calender[Date)

var b=CALCULATE(COUNTROWS('Data_Arrivals with Case Priorit'),FILTER('Data_Arrivals with Case Priorit','Data_Arrivals with Case Priorit'[Arrival Date]>=a))

return

IF(b=BLANK(),0,b)

My calender date script is CALENDAR(date(2020,10,01),today() as my client wants to last refresh date as end value in slicer by default.

My Total booked measure is not working properly and it always gives data for last refresh date only even though if i make any dat selection in slicer

Let me know your suggestions pls

[v-lionel-msft]

Hi @Vamshi2020 ,

Or try this formula.

Total Booked = 
var a= maxx( ALLSELECTED(Calender), Calender[Date)
var b=
CALCULATE(
    COUNTROWS('Data_Arrivals with Case Priorit'),
    FILTER(
        'Data_Arrivals with Case Priorit',
        'Data_Arrivals with Case Priorit'[Arrival Date]>= a
    )
)
return
IF(
    b=BLANK(),
    0,b
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AllisonKennedy]

I don't fully understand what you're trying to do - can you share screenshots or sample file?

The syntax for your DAX has some errors - what table are you using for VAR a?

For the calendar 'script' are you saying that you have created your Calendar/DimDate table in DAX using the Calendar function? Or do you have two calendar tables - one for the slicer and one for other? What relationships are there between these tables?

PS, I recommend creating the calendar table using M code, not DAX, if possible. It can still update to today's date (as of last refresh). https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html