Forum Discussion

viorelcazacu's avatar
viorelcazacu
Regular Visitor
5 years ago
Solved

Error in creating Date value

Hello,

 

I get an error when I want to create a date value with the DATE(Year, Month, Day) function.

 

The function that gives error is this: DATE([MaxDateYear],[MaxDate],1)

 

where MaxDate:=month(max(VentasSeccion[Fecha])) that should be the Month number

and MaxDateYear:=YEAR(max(VentasSeccion[Fecha])) that should be the Year number

 

I guess that those 2 measures that result in Month and Year (which are correct) does not have the format as numbers (Integers) and the Date function does not recognize them.

 

Many thanks for the support!

Viorel

Need Help
  • lbendlin's avatar
    lbendlin
    5 years ago

    You cannot use measures in your filter like that. Use variables.

  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi viorelcazacu ,

     

    Put the 2 variables in the formula,such as below:

    SalesMTD CY = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[Fecha]>=DATE(YEAR(MAX('Table'[Fecha])),MONTH(MAX('Table'[Fecha])),1)&&'Table'[Fecha]<EOMONTH(MAX('Table'[Fecha]),0)))

    And you will see:

     

    For the sample .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

6 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    You cannot use measures in your filter like that. Use variables.

    • viorelcazacu's avatar
      viorelcazacu
      Regular Visitor
      5 years ago

      Hello,

       

      Unfortunatedly it does not work. I tried to encapsulate the two measures in Value, nothing happens (the formula does not gives error, it just shows nothing (like there is no data)

       

      MaxDate:=value(month(max(VentasSeccion[Fecha])))

      MaxDateYear:=value(YEAR(max(VentasSeccion[Fecha])))

       

      I even tried to put also value inside the target formula, with no result:

      DATE([value(MaxDateYear]),value([MaxDate]),1)

       

      The formula works only if I hard code the Year and Number:

       

      SalesMTD CY:=CALCULATE(

      [Total Sales],

      FILTER(
      ALL(DateCalendarTable[Calendar Date]),
      AND(
      DateCalendarTable[Calendar Date] >= DATE(2021,3,1),
      DateCalendarTable[Calendar Date] <= [MaxDateSalesTbl]

      )))

       

      Kind regards,

      Viorel

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        5 years ago

        Hi viorelcazacu ,

         

        Put the 2 variables in the formula,such as below:

        SalesMTD CY = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[Fecha]>=DATE(YEAR(MAX('Table'[Fecha])),MONTH(MAX('Table'[Fecha])),1)&&'Table'[Fecha]<EOMONTH(MAX('Table'[Fecha]),0)))

        And you will see:

         

        For the sample .pbix file,pls see attached.

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my post as a solution!

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    2 years ago

    An ez solution I found is to split the column by the separator "/", now u have dd mm yyyy, after that u add all of them into a new column with this "Text.PadStart(Text.From([Day]), 2, "0") & "/" & Text.PadStart(Text.From([Month]), 2, "0") & "/" & Text.From([Year])", with this u can change it into date.