Forum Discussion

Rsanjuan's avatar
Rsanjuan
Icon for Advocate III rankAdvocate 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
Icon for Advocate III rankAdvocate 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
Icon for Helper V rankHelper 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