# Filter Context Confusion and use of CALCULATE

#### Purplehazed

##### New Member
In PowerBI I am creating some personal spending metrics. One is an average cost per meal. I am trying to
build a table the shows amount spent on grocery and an average \$ per meal.

I am struggling with a measure that calculates the days in a month that will be the denominator in the \$ per meal calculation.

In the pivot table pasted below and am getting 67 from the measure below. I thought that using CALCULATE in the measure
would prevent getting the same result each month i.e. the pivot tables filter would control the filter context.

I expected 28 days for Feb, 31 days for Mar and 26 days for Apr and April would go up a day each day until the end of the month then start over with May.

My date table example skips a few days so ignore 67, that number should be more like 85.

Any thoughts are appreciated!

 Row Labels Sum of Amount Test February (775) 67 March (898) 67 April (509) 67 Grand Total (2,181) 67

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Test := CALCULATE(COUNTROWS(FILTER('Date','Date'[Today]>'Date'[Date])))

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Purplehazed

##### New Member
I think I figured out the problem.

I built a simple two table model to be used to ask the question. In a measure I used CALCULATE correctly (I think) but did not get the expected result. When I added a "month" column via a calculated column in the lookup date table and pulled that column into the pivot table I got this (below).

 Row Labels Sum of Amount Test 2 (775) 20 3 (898) 27 4 (509) 20 Grand Total (2,181) 67

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Any explanation of what I've done and/or not done correctly in non-programmer language would still be much appreciated.

I am determined to learn DAX!!!! Very powerful!

Replies
8
Views
2K
Replies
3
Views
2K
Replies
0
Views
456
Replies
0
Views
553
Replies
4
Views
2K