DAX - CALCULATE and SUMX

[Wishmaster89]

Hello

I have written a formula which calculates completion dates in August and this works fine.

I tried to have a go at rewriting it using SUMX but i get this error " "A table of multiple values was supplied where a single value was expected"

CALCULATE formula =

Completions August = CALCULATE(Count('Learning Plan'[Completion Date]),
                     DATESBETWEEN('Learning Plan'[Completion Date],
                     DATE(2023,8,1),
                     DATE(2023,8,31)
                     )
                     ,  'Learning Plan'[EPA Grade]="Distinction"
)

SUMX version

sumxAugCompletions = COUNTX(FILTER('Learning Plan',  DATESBETWEEN('Learning Plan'[Completion Date],
                     DATE(2023,8,1),
                     DATE(2023,8,31)
                     )),[Countofcomp])

 

[Shortmeister1]

Hi WishMaster.

FILTER(<table>,<filter>) --> Returns a Table.

DATESBETWEEN(<Dates>,<StartDate>,<EndDate>) --> Returns a table.

Your version has DatesBetween in the filter position of the FILTER function.

i.e. the error message "A table of multiple values was supplied where a single value was expected" is referring to the second parameter of FILTER function.

 

[Wishmaster89]

Hi WishMaster.

FILTER(<table>,<filter>) --> Returns a Table.

DATESBETWEEN(<Dates>,<StartDate>,<EndDate>) --> Returns a table.

Your version has DatesBetween in the filter position of the FILTER function.

i.e. the error message "A table of multiple values was supplied where a single value was expected" is referring to the second parameter of FILTER function.

Hello thankyou for your reply, yes thats right the datesbetween is in the filter function as i want to filter completion dates between 01/08 and 31/08 like i do in the calculate function. Is there any way to fix this?

 

[Shortmeister1]

Struggling to do this without access to the report, but how about removing the FILTER function and just using DATESBETWEEN? They both produce a table output.

 

[Wishmaster89]

Struggling to do this without access to the report, but how about removing the FILTER function and just using DATESBETWEEN? They both produce a table output.

Thanks for the suggestion, i will try tomorrow.