Help needed for DAX rolling calculation

[Rsanjuan]

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 to calculate the number of meetings in the past 60 days, while the planned meetings I'm trying to calculate the planned meetings in the future.  I believe I got the past 60 days calculation correct, but it's totaling wrong with the "61".  Also, I am not sure how to do the calculation for the planned meetings.

I had invoked a table using the script found here:

http://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

If someone could help me with the DAX formula, that would be great!  Thanks!

[Nhallquist]

What formula are you using to count the number of meetings in the past 60 days?  Are you counting the date, or a unique meeting identifier.  Ideally, you want to count the Index.  If you don't have one, you can always try Sum(Last60Days).  Then you would be adding a 1 for every meeting occurrance.  

For meetings in the future, I would stay with what you did for Meetings past, and do something like...

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

Then you can use Sum([FutureMeetingFlag])

Let  us know if this resolves the issue, 

Nate

[Rsanjuan]

@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]

@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]

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

Thanks,

Nate

[Rsanjuan]

@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]

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]

@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

[Nhallquist]

Check the settings of your value field, and make sure that some weird setting isn't checked.

[Rsanjuan]

@Nhallquist

Everything looks ok.

Completed60 = sum('Invoked Function'[Last60Days])

PlannedFuture = Sum('Invoked Function'[FutureMeeting])

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

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

Is there a way just to not show the Total at all?

[Nhallquist]

you should be able to just check the menu item from above that states "Don't Summarize".  

[Rsanjuan]

@Nhallquist

Now it's just saying 1 or 0 for each one.  saying "true" or "false" that they had that type of meeting in the past 60 days.

I think I have to change the formula in for 

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

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

Like it meets the Date condition, something like count it, instead of true/false.  Any ideas on the type of formula to use?  Thanks!