Forum Discussion
Help needed for DAX rolling calculation
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
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
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
Check the settings of your value field, and make sure that some weird setting isn't checked.
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?