How to change my datesbetween function dynamically?

[mith_tina]

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

[Anonymous · Accepted solution]

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]

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

[deevaker]

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]

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.