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:=mo...
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!

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!

    • viorelcazacu's avatar
      viorelcazacu
      Regular Visitor
      5 years ago

      Dear v-kelly-msft ,

       

      Your solution works like a charm as I cannot use variables (the work Powerpivot is version 2013):

       

      SalesMTD CY:=CALCULATE(

      [Total Sales],

      FILTER(ALL(DateCalendarTable[Calendar Date]),

      DateCalendarTable[Calendar Date]>=DATE(YEAR(MAX(VentasSeccion[Fecha])),MONTH(MAX(VentasSeccion[Fecha])),1)

      &&DateCalendarTable[Calendar Date]<EOMONTH(MAX(VentasSeccion[Fecha]),0))
      )

       

      Many thanks and kind regards,

      Viorel