Forum Discussion

mith_tina's avatar
mith_tina
Frequent Visitor
4 years ago
Solved

How to change my datesbetween function dynamically?

i write my code like below. I connect with my date table but when i use date table i am getting total value. It is not filtering the value. so i use dates between function and use the table's date column instead of my date table. now i am getting the right value but i have to change every month manually like next month i need to change "7/1/2022","7/31/2022". how can i write the below code, so i dont need to change dates every month?

 

 

Current_month_Candidate=IF(EMT[MG]="TC",(CALCULATE(COUNT(Candidate[JReq]), Candidate[Job level] in {"B","C","D"},DATESBETWEEN(Candiadte[Added Date],"6/1/2022","6/30/2022"))),

 

IF(EMT[MG]="LG",(CALCULATE(COUNT(Candidate[JReq]), Candidate[Job Family] in {"Analyst","Developer"},Candidate[Job level] in {"B","C","D"},DATESBETWEEN(Candiadte[Added Date],"6/1/2022","6/30/2022")))

 

 

 

I have tried below code  also but i am not getting any value.

Current_month_Candidate=IF(EMT[MG]="TC",(CALCULATE(COUNT(Candidate[JReq]), Candidate[Job level] in {"B","C","D"},DATESMTD(dateadd('Date'[Date],-1,MONTH)))

 

 

Thanks

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi mith_tina ,

    Please have a try.

    Create columns first.

    Year_column=Year(Candiadte[Added Date])
    Month_column=Month(Candiadte[Added Date])

    Then create a measure.

    Current_month_Candidate =
IF (
    MAX ( EMT[MG] ) = "TC",
    (
        CALCULATE (
            COUNT ( Candidate[JReq] ),
            FILTER (
                ALL ( Candiadte ),
                Candidate[JobLevel]
                    IN { "B", "C", "D" }
                        && Candiadte[month_column] = SELECTEDVALUE ( Candiadte[month_column] )
                        && Candiadte[Year_column] = SELECTEDVALUE ( Candiadte[Year_column] )
            )
        )
    )
)

     

    If I have misunderstood your meaning, please provide your pbix file without privacy information and your desired output with more details.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi mith_tina ,

    Please have a try.

    Create columns first.

    Year_column=Year(Candiadte[Added Date])
    Month_column=Month(Candiadte[Added Date])

    Then create a measure.

    Current_month_Candidate =
IF (
    MAX ( EMT[MG] ) = "TC",
    (
        CALCULATE (
            COUNT ( Candidate[JReq] ),
            FILTER (
                ALL ( Candiadte ),
                Candidate[JobLevel]
                    IN { "B", "C", "D" }
                        && Candiadte[month_column] = SELECTEDVALUE ( Candiadte[month_column] )
                        && Candiadte[Year_column] = SELECTEDVALUE ( Candiadte[Year_column] )
            )
        )
    )
)

     

    If I have misunderstood your meaning, please provide your pbix file without privacy information and your desired output with more details.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • deevaker's avatar
    deevaker
    Resolver I
    4 years ago

    Hi mith_tina 
    You can try using below

     

    aa=
    Var _Start = DATE(Year(TODAY()),Month(TODAY()),1)
    Var _End = DATE(Year(TODAY()),Month(TODAY()),DAY(TODAY()))
    return
    IF(EMT[MG]="LG",(CALCULATE(COUNT(Candidate[JReq]), Candidate[Job Family] in {"Analyst","Developer"},Candidate[Job level] in {"B","C","D"},DATESBETWEEN(Candiadte[Added Date],_Start,_End)))
     
    Thanks,
    Deevaker
    +91-9711975011

     

     

    • mith_tina's avatar
      mith_tina
      Frequent Visitor
      4 years ago

      THnaks Deevakar for your quick response. code is not working for me.,no value is showing. i am trying to show Month june's value.

  • deevaker's avatar
    deevaker
    Resolver I
    4 years ago
    The above measure is for current month, if you would like for june (previous month) you can update the measure accordingly, like below 
     
    aa=
    Var _Start = DATE(Year(TODAY()),Month(TODAY())-1,1)
    Var _End = DATE(Year(TODAY()),Month(TODAY()),1)
    return
    IF(EMT[MG]="LG",(CALCULATE(COUNT(Candidate[JReq]), Candidate[Job Family] in {"Analyst","Developer"},Candidate[Job level] in {"B","C","D"},DATESBETWEEN(Candiadte[Added Date],_Start,_End)))


    If you would like we can connect and discuss this. Feel free to reach out on whatsapp +91-9711975011 or skype - deevaker1
     
    Thanks,
    Deevaker