Forum Discussion

Rsanjuan's avatar
Rsanjuan
Advocate III
10 years ago

Help needed for DAX rolling calculation

Last60Days = IF(AND([Date] >= [Today]-60, [Date] <= [Today]),1,0)   I am trying to calculate the rolling number of completed and planned meetings.     For the completed meetings, I'm trying...
Need Help
Rsanjuan's avatar
Rsanjuan
Advocate III
10 years ago

Nhallquist

 

thanks for the reply.  I don't have a separate formula to count the number of meetings in the past 60 days.  

 

In the field "status" there are two types;  Completed Meetings and Planned Meetings.

 

I just set the rows for the account person and the columns to the "status" field.  I used the 60day calculation as a visual filter. 

 

I will try your suggestions.  Thanks!

Rsanjuan's avatar
Rsanjuan
Advocate III
10 years ago

Nhallquist

 

I tried the future formula, it seems to work, but it's a bit off.  The actual data has 4, but it's only showing 3.  And it is totaling wrong :(

 

  • Nhallquist's avatar
    Nhallquist
    Helper V
    10 years ago

    Please post your formulas.  This will help me understand what is going on better.

     

    Thanks,

     

    Nate

    • Rsanjuan's avatar
      Rsanjuan
      Advocate III
      10 years ago

      Nhallquist

       

      Here are the formulas I have:

       

      In the calendar table that I invoked, I have the following added columns:

       

      FutureMeeting = If([Date]>Today(), 1,0)

      Last60Days = (IF(AND([Date] >= [Today]-60, [Date] <= [Today]),1,0))

       

      In the calendar table, I also have this measure:   Today = DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))

       

      In the meeting report table, I have the following measures added:

       

      Completed Meetings = CALCULATE(COUNTROWS('Meeting Report'), 'Meeting Report'[Status]="Completed Meetings")

      Planned Meetings = CALCULATE(COUNTROWS('Meeting Report'),'Meeting Report'[Status]="Planned Meetings")

       

      So, I'm trying to accomplish the following:

       

      # of Completed Meetings in past 60 days per Name

      # of Planned Meetings in the future per Name

       

       

       

       

      • Nhallquist's avatar
        Nhallquist
        Helper V
        10 years ago

        I always prefer to use the Sum function whenever I can, rather than counting something.  To make sure we are getting the data and to make sure we are not missing something with counting, can you replace the CountRows function and replace it with Sum (Last60Days) and Sum(FutureMeeting)?  

         

        Also, do you have a unique Row Index for every line in the data?  I know that sometimes, without a Unique Index/Key/RowID, counting Rows can be problematic.

         

        Let me know what you get.

         

        Nate