Forum Discussion
Relative date slicer not aggregating properly
Hello all,
I've created a graph with average cost. It uses the following measure. [Costs] is another measure.
Average Cost per Handling =
Var _Handlings = MAX(CALCULATE(COUNT(Table1[Seq]),Table1[HandlingType]="Paid",Table1[Location]="US"),1)
Return
[Costs] / _Handlings
I want to show the past few weeks in a graph, but somehow it has a spike in the first week which messis up the visual.
As you can see, 2019 week 50 has a really high average cost (it doesn't matter which week, it's always the first one in the visual that's high as shown).
When I change the relative filter to days instead of calendar weeks, the data show correctly.
What am I missing here?
I figured it out by looking at which dates it displays when I zoom in on that last week. Because PowerBI's weeks start on Sunday, but my weeks (in my datetable) start on Monday, it shows only the Sunday of that first week. There are always very few handlings done on Sunday, if at all, but my costs are based on a per-day-basis due to maintenance and depreciation costs. That's why the average cost is so high.
1 Reply
I figured it out by looking at which dates it displays when I zoom in on that last week. Because PowerBI's weeks start on Sunday, but my weeks (in my datetable) start on Monday, it shows only the Sunday of that first week. There are always very few handlings done on Sunday, if at all, but my costs are based on a per-day-basis due to maintenance and depreciation costs. That's why the average cost is so high.