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. 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's avatar
    BILASolution
    Icon for Solution Specialist rankSolution 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's avatar
      IoanCosmin
      Icon for Helper III rankHelper 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 :(

      • Anonymous's avatar
        Anonymous
        Not applicable

        Could you share the data that you are using