Error in creating Date value

[viorelcazacu]

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

[lbendlin · Accepted solution]

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

[Syndicate_Admin]

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.

[lbendlin · Accepted solution]

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

[lbendlin]

Encapsulate your measure results in VALUE()

VALUE function (DAX) - DAX | Microsoft Docs

[viorelcazacu]

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 · Accepted solution]

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]

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