An Argument of Function Date has wrong data type or result is too small or too large

[shivakoriginja]

Getting the error 'An Argument of Function Date has wrong data type or result is too small or too large' when the following measure is included in any graph. However, the Value is shown properly working in KPI cards.

 Sales TY = var a = DATE(YEAR(MAX(VW_Primary_Sales_Transaction[BillingDocDate])),MONTH(MAX(VW_Primary_Sales_Transaction[BillingDocDate])),1)

var b = DATE(YEAR(MAX(VW_Primary_Sales_Transaction[BillingDocDate])),MONTH(MAX(VW_Primary_Sales_Transaction[BillingDocDate])),DAY(MAX(VW_Primary_Sales_Transaction[BillingDocDate])))

var year= IF(MONTH(TODAY()) in {1,2,3},YEAR(TODAY())-1,YEAR(TODAY()))

var cd = DATE(year,4,1)

var c=DATE(YEAR(MAX(VW_Primary_Sales_Transaction[BillingDocDate])),1,1)

var mtd_value = CALCULATE(SUM(VW_Primary_Sales_Transaction[NetValue])/100000,FILTER(Date_Dimention,Date_Dimention[Date]>=a &&Date_Dimention[Date]<=b)

)

var ytd_value = CALCULATE(SUM(VW_Primary_Sales_Transaction[NetValue])/100000,FILTER(Date_Dimention,Date_Dimention[Date]>=c && Date_Dimention[Date]<=b)

)

var mtd_volume = CALCULATE(SUM(VW_Primary_Sales_Transaction[BillQuantityInAH]),FILTER(Date_Dimention,Date_Dimention[Date]>=a && Date_Dimention[Date]<=b)

)

var ytd_volume = CALCULATE(SUM(VW_Primary_Sales_Transaction[BillQuantityInAH]),FILTER(Date_Dimention,Date_Dimention[Date]>=c && Date_Dimention[Date]<=b)

)

var res =

IF(SELECTEDVALUE(MTDYTD[Name])="MTD" && SELECTEDVALUE(ValueV[Name])="Value",mtd_value,

IF(SELECTEDVALUE(MTDYTD[Name])="MTD" && SELECTEDVALUE(ValueV[Name])="Volume",mtd_volume,

IF(SELECTEDVALUE(MTDYTD[Name])="YTD" && SELECTEDVALUE(ValueV[Name])="Value",ytd_value,

IF(SELECTEDVALUE(MTDYTD[Name])="YTD" && SELECTEDVALUE(ValueV[Name])="Volume",ytd_volume))))

return res

[poli361]

For me solution didn't work out . The problem happened because  my Calendar Table didnt have every possible dates from the fact tables . So I had 2 solutions to this problem : 1) filter Calendar[Date] is not blank() 
                                                                         2) Fix Dates of Calendar

[amitchandak]

@shivakoriginja , I do see anything wrong in formula. Buy as year is keyword too. Rename the year variable as _year and use that

Sales TY = var a = DATE(YEAR(MAX(VW_Primary_Sales_Transaction[BillingDocDate])),MONTH(MAX(VW_Primary_Sales_Transaction[BillingDocDate])),1)
var b = DATE(YEAR(MAX(VW_Primary_Sales_Transaction[BillingDocDate])),MONTH(MAX(VW_Primary_Sales_Transaction[BillingDocDate])),DAY(MAX(VW_Primary_Sales_Transaction[BillingDocDate])))

var _year= IF(MONTH(TODAY()) in {1,2,3},YEAR(TODAY())-1,YEAR(TODAY()))
var cd = DATE(_year,4,1)

var c=DATE(YEAR(MAX(VW_Primary_Sales_Transaction[BillingDocDate])),1,1)

var mtd_value = CALCULATE(SUM(VW_Primary_Sales_Transaction[NetValue])/100000,FILTER(Date_Dimention,Date_Dimention[Date]>=a &&Date_Dimention[Date]<=b)
)
var ytd_value = CALCULATE(SUM(VW_Primary_Sales_Transaction[NetValue])/100000,FILTER(Date_Dimention,Date_Dimention[Date]>=c && Date_Dimention[Date]<=b)
)
var mtd_volume = CALCULATE(SUM(VW_Primary_Sales_Transaction[BillQuantityInAH]),FILTER(Date_Dimention,Date_Dimention[Date]>=a && Date_Dimention[Date]<=b)
)
var ytd_volume = CALCULATE(SUM(VW_Primary_Sales_Transaction[BillQuantityInAH]),FILTER(Date_Dimention,Date_Dimention[Date]>=c && Date_Dimention[Date]<=b)
)
var res =
IF(SELECTEDVALUE(MTDYTD[Name])="MTD" && SELECTEDVALUE(ValueV[Name])="Value",mtd_value,
IF(SELECTEDVALUE(MTDYTD[Name])="MTD" && SELECTEDVALUE(ValueV[Name])="Volume",mtd_volume,
IF(SELECTEDVALUE(MTDYTD[Name])="YTD" && SELECTEDVALUE(ValueV[Name])="Value",ytd_value,
IF(SELECTEDVALUE(MTDYTD[Name])="YTD" && SELECTEDVALUE(ValueV[Name])="Volume",ytd_volume))))

[shivakoriginja]

 Sales LY = var a = DATE(YEAR(MAX(VW_Primary_Sales_Transaction[BillingDocDate]))-1,MONTH(MAX(VW_Primary_Sales_Transaction[BillingDocDate])),1)

var b = DATE(YEAR(MAX(VW_Primary_Sales_Transaction[BillingDocDate]))-1,MONTH(MAX(VW_Primary_Sales_Transaction[BillingDocDate])),DAY(MAX(VW_Primary_Sales_Transaction[BillingDocDate])))

var _year= IF(MONTH(TODAY()) in {1,2,3},YEAR(TODAY())-2,YEAR(TODAY())-1)

var cd = DATE(_year,4,1)

var c=DATE(YEAR(MAX(VW_Primary_Sales_Transaction[BillingDocDate]))-1,1,1)

var mtd_value = CALCULATE(SUM(VW_Primary_Sales_Transaction[NetValue])/100000,FILTER(ALL(Date_Dimention),Date_Dimention[Date]>=a &&Date_Dimention[Date]<=b)

)

var ytd_value = CALCULATE(SUM(VW_Primary_Sales_Transaction[NetValue])/100000,FILTER(ALL(Date_Dimention),Date_Dimention[Date]>=c && Date_Dimention[Date]<=b)

)

var mtd_volume = CALCULATE(SUM(VW_Primary_Sales_Transaction[BillQuantityInAH]),FILTER(ALL(Date_Dimention),Date_Dimention[Date]>=a && Date_Dimention[Date]<=b)

)

var ytd_volume = CALCULATE(SUM(VW_Primary_Sales_Transaction[BillQuantityInAH]),FILTER(ALL(Date_Dimention),Date_Dimention[Date]>=c && Date_Dimention[Date]<=b)

)

var res =

IF(SELECTEDVALUE(MTDYTD[Name])="MTD" && SELECTEDVALUE(ValueV[Name])="Value",mtd_value,

IF(SELECTEDVALUE(MTDYTD[Name])="MTD" && SELECTEDVALUE(ValueV[Name])="Volume",mtd_volume,

IF(SELECTEDVALUE(MTDYTD[Name])="YTD" && SELECTEDVALUE(ValueV[Name])="Value",ytd_value,

IF(SELECTEDVALUE(MTDYTD[Name])="YTD" && SELECTEDVALUE(ValueV[Name])="Volume",ytd_volume))))

return res

Thanks, it's working for sales ty but when I use last year's measure it's getting the same error.

[amitchandak]

@shivakoriginja , Do see any error in that. Just make sure you have click outside the measure box after saving the changes

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.