DAX measure with month variable based on date field

Question

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))

I would appreciate if someone would suggest a different way of achieving the same result.

LE:

This is a mock-up of what I am trying to achieve:

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.

My tables look like this:

+-----------+--------------------+----------+
|  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  |
+----------+--------+----------+---------------+--------+-----+--------+

Do your 'All Cars Inspected' table and your Targets tables have any relationships? This is pretty important to specify. — Alexis Olson
Hello Alexis! There's no relationship between the 2, maybe I can use the 'Location' field but that's about it. Can you please help me to get it to work? — Cosmin
I think you probably want a calendar table to relate these two. If you could post a mock-up of what you are trying to achieve, that would be helpful as well. — Alexis Olson
@AlexisOlson I was thinking about that too, but the targets don't really keep track of the year. I have updated my question. Is there a easy and smart way to go about this? Thank you. — Cosmin

Alexis Olson Alexis Olson · Accepted Answer · 2018-02-01T20:33:22

Let the Value for your gauge be the sum of TotalCarsInspected and set the Maximum value to the following measure:

Cars Inspected = 
VAR selectedMonth = MONTH(MAX('All Cars Inspected'[Workday]))
RETURN LOOKUPVALUE(Targets[Target],
           Targets[Location], "Texas",
           Targets[Description], "CarsInspected",
           Targets[sMonth], selectedMonth)

DAX measure with month variable based on date field

1 Answers