DAX measure with month variable based on a date field

[IoanCosmin]

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

[BILASolution]

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]

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 😞

[BILASolution]

With your own data is much better but I tried to simulate your case with fictitious data and the measure works...

[IoanCosmin]

Here's my data and what I am trying to achieve:

+-----------+--------------------+----------+
| Workday | TotalCarsInspected | Location |
+-----------+--------------------+----------+
| 4/4/2017 | 1 | Texas |
| 4/11/2017 | 149 | Texas |
| 4/12/2017 | 129 | Texas |
| 4/13/2017 | 201 | Texas |
| 4/14/2017 | 4 | Texas |
| 4/15/2017 | 6 | Texas |
+-----------+--------------------+----------+

+----------+--------+----------+---------------+--------+-----+--------+
| TargetID | sMonth | Location | Description | Target | Red | Yellow |
+----------+--------+----------+---------------+--------+-----+--------+
| 495 | 1 | Texas | CarsInspected | 3636 | 0.5 | 0.75 |
| 496 | 2 | Texas | CarsInspected | 4148 | 0.5 | 0.75 |
| 497 | 3 | Texas | CarsInspected | 4861 | 0.5 | 0.75 |
| 498 | 4 | Texas | CarsInspected | 4938 | 0.5 | 0.75 |
| 499 | 5 | Texas | CarsInspected | 5094 | 0.5 | 0.75 |
| 500 | 6 | Texas | CarsInspected | 5044 | 0.5 | 0.75 |
| 501 | 7 | Texas | CarsInspected | 5043 | 0.5 | 0.75 |
| 502 | 8 | Texas | CarsInspected | 4229 | 0.5 | 0.75 |
| 503 | 9 | Texas | CarsInspected | 4311 | 0.5 | 0.75 |
| 504 | 10 | Texas | CarsInspected | 4152 | 0.5 | 0.75 |
| 505 | 11 | Texas | CarsInspected | 3592 | 0.5 | 0.75 |
| 506 | 12 | Texas | CarsInspected | 3748 | 0.5 | 0.75 |
+----------+--------+----------+---------------+--------+-----+--------+

The total cars get filtered by the Workday. I would like to make the Targets/Ranges dynamic. When the slider gets adjusted everything else is adjusted.

Thank you

[BILASolution]

Hi again @IoanCosmin

Check this pbix.

https://1drv.ms/f/s!AuU-Ye8UGM4Rko0dyKxTaxuA6pAP8A

BILASolution

[IoanCosmin]

Hello @BILASolution,

Thank you for your time and support. This is not really what I am looking for. I probably wasn't clear enough with my last reply.

I have to different tables, one with the targets and one with the actual cars inspected. Your example summarize only the cars inspected table and it considers it to be the target. The point is, I have 2 different tables, one has an actual date and the other takes into account only the month and I am trying to tie them together with a measure.

Can you still help please?

Thank you

[BILASolution · Accepted solution]

@IoanCosmin

Try this one...

https://1drv.ms/f/s!AuU-Ye8UGM4Rko0dyKxTaxuA6pAP8A

BILASolution

[IoanCosmin]

@BILASolution

Thank you!

[Anonymous]

Could you share the data that you are using