Purplehazed
New Member
- Joined
- Jul 7, 2014
- Messages
- 13
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!
<colgroup><col><col><col></colgroup><tbody>
</tbody>
Test := CALCULATE(COUNTROWS(FILTER('Date','Date'[Today]>'Date'[Date])))
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])))