Forum Discussion

rjekrej's avatar
rjekrej
Frequent Visitor
3 years ago

Question regarding MAX DAX formula concerning date table and fact table

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

 

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      rjekrej
      Frequent Visitor
      3 years ago

      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?