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!

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