Forum Discussion
DAX measure with month variable based on a date field
I am having a hard time getting the following measure to work. I am trying to change the target based on a date filter. My filter is the Workday columns, where Workday is a standard date column. sMonth is a month columns formatted as whole number. I am looking to keep the slicer granular, in order to work by day, adding custom columns with month and year and basing the measure on those would help. This is what I have tried and couldn't get it to work:
Cars Inspected =
VAR
selectedMonth = MONTH(SELECTEDVALUE('All Cars Inspected'[Workday]))
RETURN CALCULATE(SUM(Targets[Target]),
FILTER(Targets,Targets[Location]="Texas"),
FILTER(Targets,Targets[Description]="CarsInspected"),
FILTER(Targets,Targets[sMonth]=selectedMonth))would appreciate if someone would suggest a different way of achieving the same result or what do I need to tweak.
Thank you,
C
9 Replies
- BILASolution
Solution Specialist
Hi IoanCosmin
Try this measure...
Cars Inspected = VAR selectedMonth = MONTH(SELECTEDVALUE('All Cars Inspected'[Workday])) RETURN CALCULATE(SUM(Targets[Target]), ALL(Targets), FILTER(Targets,Targets[Location]="Texas"), FILTER(Targets,Targets[Description]="CarsInspected"), FILTER(Targets,Targets[sMonth]=selectedMonth))I hope it helps
BILASolution
- IoanCosmin
Helper III
Hello BILASolution
I tried it and I still get a blank. I also tried this variation:
Cars Inspected = VAR selectedMonth = MONTH(SELECTEDVALUE('All Cars Inspected'[Workday])) RETURN CALCULATE(SUM(Targets[Target]), FILTER(ALL(Targets), Targets[Location]="Texas" && Targets[Description]="CarsInspected" && Targets[sMonth]=selectedMonth))... same result :(
- AnonymousNot applicable
Could you share the data that you are using