Forum Discussion

srjohnson212's avatar
srjohnson212
Frequent Visitor
2 years ago
Solved

Divide columns within date range

I am trying to calculate the % promotions in the last 12 months.  I have a Calendar table that I'm using along with my data set.  To get the overall % promotions I am using:

% Promoted =

    DIVIDE(

       SUM('Emp_Table'[# of Promotions]),

       COUNTA('Emp_Table'[Emp_ID])

 

How do I return only results for the last 12 months?

 

Thanks, Shannon

  • Dangar332's avatar
    Dangar332
    2 years ago

    hi, srjohnson212 

    try below

    % Promoted =

    calculate(DIVIDE(

       SUM('Emp_Table'[# of Promotions]),

       COUNTA('Emp_Table'[Emp_ID]),
        dateadd('calender table'[date],-12,month)
   )

3 Replies

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    hi, srjohnson212 

    try below

    % Promoted =

    calculate(DIVIDE(

       SUM('Emp_Table'[# of Promotions]),

       COUNTA('Emp_Table'[Emp_ID]),
        dateadd('calender table'[date],-12,month)
   )
    • srjohnson212's avatar
      srjohnson212
      Frequent Visitor
      2 years ago

      Hi Dangar332 , that didn't work unfortunately.  I was trying to use something similar except I used datesinperiod instead of dateadd.  Do you have any other suggestions?

       

      Thanks.

      • srjohnson212's avatar
        srjohnson212
        Frequent Visitor
        2 years ago

        Dangar332 Apologies, once I moved the parenthesis to the proper place, it worked.  Thank you!!