Question regarding MAX DAX formula concerning date table and fact table

[Caze982]

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

 

[Matt Allington]

if your dim[date] column is connected to fact[date] in your model, and if you are using dim[date] in your visual, not fact[date], then the first formula is correct. My guess is you are using fact[date] in your visual, which if you are, is wrong. its good practice to hide fact[date] unless there is some specific reason to keep it visible.