Question regarding MAX DAX formula concerning date table and fact table

[rjekrej]

Hi everyone 

I have a star schema model where i have some dimension tables and a fact table. The dates in the dimension date table go to the end of the year 2025. The fact table date is always to days date.

I want to calculate visits year-to-date but the formula below (v.1) does not work but v.2 do. Can anyone explain why v.2 works and v.1 does not?

YTD (v.1) =

CALCULATE ( [Visits], DATESYTD ( Dim_dato[Date] ) )

YTD (v.2) =

CALCULATE (

    CALCULATE ( [Visits], DATESYTD ( Dim_dato[Date] ) ),

    Dim_dato[Date] <= MAX ( 'Fact_besøg'[Date] )

)

[v-rzhou-msft]

Hi @rjekrej ,

Normally, if your Dim_dato table has continuous single date and is connected to Fact_besøg table, DATESYTD() should work in this situation.

My Sample:

Dim_dato = CALENDARAUTO() 

Result is as below.

You can check whether your data model is same as mine. Your issue should be caused by the calculation logic of [Visit] measure. In my sample, [Visit] measure is only a sum measure. You can check yours as well.

Best Regards,
Rico Zhou

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

[rjekrej]

Hi 

Thank you very much for you help. It was great, it works. In my calender dim table i have a value for the date 1/1/1900 ( i cannot remove it) along with date values for the year 2022 and 2023. Do you know in DAX how exclude the 1/1/1900 and still make the formula work? 

[amitchandak]

@rjekrej , You can try measure like

YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

or

YTD =
var _max = MAX( 'Date'[Date])
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))