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

 

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

      • Rsanjuan's avatar
        Rsanjuan
        Advocate III
        10 years ago

        Nhallquist

         

        I tried it and I got this.  Looks like the account name and their totals are accurate, just the overall quota seems way off.  It's not totaling correctly