Forum Discussion

619SK's avatar
619SK
Helper II
2 years ago
Solved

Dynamic Month value while using calculate and filter

How can i create dynamic value to use below month value in calculate & filter formulas

 

Calculate([Distcoun tofnumberMarid],Filter('Global',[mmyy]="Q1-2024 Mar",'Global',[Std]="Yes",Global',[Month]="Mar-24",'Global",[Testing month]="Mar"))))

 

 

 

ABC DescriptionSample (Mar)Passing % _ABCPassing % XYZ
TEST 14090%80%
If Pending from prev month0  
TEST 33090%90
TEST 44080%100
TEST 54070%100
    
    
Table view in BI  
    
Sample (Dist Count for Mar) 
Calculate([Distcoun tofnumberMarid],Filter('Global',[mmyy]="Q1-2024 Mar",'Global',[Std]="Yes",Global',[Month]="Mar-24",'Global",[Testing month]="Mar"))))
General Comment
  • PijushRoy's avatar
    PijushRoy
    2 years ago

    Hi 619SK 

    I hope from measure, you will get Idea to create Dynamic value and as per your requirement you can update it. I am taking dynamic value as Current Month, Current Qtr and Current Year

     

    = VAR _CurrentQuarterYearMonth = "Q"&QUARTER(Today())&"-"&YEAR(TOday())&" "&FORMAT(Today(),"Mmm")
VAR _CurrentQuarterMonth = FORMAT(Today(),"Mmm")&"-"&YEAR(TOday())
VAR _CurrentMonth = FORMAT(Today(),"Mmm")
RETURN
Calculate([Distcoun tofnumberMarid],Filter('Global',[mmyy]= _CurrentQuarterYearMonth ,'Global',[Std]="Yes",Global',[Month]=_CurrentQuarterMonth,'Global",[Testing month]=_CurrentMonth))))

     

     


    Let me know if that works for you


    If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

    Thanks
    Pijush
    Linkedin

1 Reply

  • PijushRoy's avatar
    PijushRoy
    Community Champion
    2 years ago

    Hi 619SK 

    I hope from measure, you will get Idea to create Dynamic value and as per your requirement you can update it. I am taking dynamic value as Current Month, Current Qtr and Current Year

     

    = VAR _CurrentQuarterYearMonth = "Q"&QUARTER(Today())&"-"&YEAR(TOday())&" "&FORMAT(Today(),"Mmm")
VAR _CurrentQuarterMonth = FORMAT(Today(),"Mmm")&"-"&YEAR(TOday())
VAR _CurrentMonth = FORMAT(Today(),"Mmm")
RETURN
Calculate([Distcoun tofnumberMarid],Filter('Global',[mmyy]= _CurrentQuarterYearMonth ,'Global',[Std]="Yes",Global',[Month]=_CurrentQuarterMonth,'Global",[Testing month]=_CurrentMonth))))

     

     


    Let me know if that works for you


    If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

    Thanks
    Pijush
    Linkedin