Forum Discussion

IoanCosmin's avatar
IoanCosmin
Icon for Helper III rankHelper III
8 years ago
Solved

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. sMon...
Need Help
BILASolution's avatar
BILASolution
Icon for Solution Specialist rankSolution Specialist
8 years ago

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's avatar
IoanCosmin
Icon for Helper III rankHelper III
8 years ago

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 :(

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Could you share the data that you are using

  • BILASolution's avatar
    BILASolution
    Icon for Solution Specialist rankSolution Specialist
    8 years ago

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

     

     

     

     

    • IoanCosmin's avatar
      IoanCosmin
      Icon for Helper III rankHelper III
      8 years ago

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

      enter image description here

       

      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