Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
You cannot use measures in your filter like that. Use variables.
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!
You cannot use measures in your filter like that. Use variables.
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
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!
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
User | Count |
---|---|
98 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
58 |