Forum Discussion
Days calculating error
Hi Anonymous
Try this for your measure, placing it in the values of a matrix with Month and Day on rows as you show. Table1 is the table containing Month and Day columns shown in the matrix visual.
Calc Target = SELECTEDVALUE ( Table1[Day] ) * DIVIDE ( CALCULATE ( COUNT ( Table1[Day] ) - 1, ALL ( Table1[Day] ) ), [BaseValue] )
Anonymous
Now on to your next measure:
Last Day =
CALCULATE (
SELECTEDVALUE ( dCalendar[Date].[Dia] );
FILTER ( ALL ( dCalendar ); dCalendar[Date] = MAX ( dCalendar[Date] ) )
)
The only difference with the previous one is that you use MAX( ) instead of MIN( ), so we can follow a very similar logic to determine how it works. Again, you have a table visual with Year, Month and Day at the beginning, all columns of table dCalendar. All three fields will be creating a filter context for the measures you include in the table visual. Again, taking the first row as an example, we have
Year=2018, Month=1, Day=1
so your dCalendar[Date] column is filtered down to that day only, 1st of January, 2018.
MAX(dCalendar[Date]) is clearly that day, 2018/01/01. Note that no matter what function we use here, MIN( ), MAX( ), AVG()... we'll get 2018/01/01 anyway, since it's the only day available in the filtered dCalendar[Date]. Like before, the result of your FILTER( ) operation is the dCalendar table with only one row, the one containing 2018/01/01 in column dCalendar[Date]. Then SELECTEDVALUE(... ) returns the day in that only row, which is 1.
And something else, your code as written would be exactly equivalent to:
Last Day = DAY(MAX ( dCalendar[Date] ) )
or am I mistaken? Give it a thought
Anonymous
OK, this is taking a bit too long. Let's try and wrap it up
We can finally build on what we have so far and see what happens with your last measures:
Interval Days = ([Last Day]-[First Day])
We've just seen that both [Last Day] and [First Day] return the same number in every row of the table visual, right? So the result of [Interval Days] will be zero. And then, we see why
Calc Target =
CALCULATE (
MAXX ( dCalendar; dCalendar[Day] * ( [BaseValue] / [Interval Days] ));
ALL ( dCalendar[Day] )
)
yields infinite. You are diving by zero in every row of the table visual.
Now, why doesn't this happen in your card visuals? Because the only filters acting on them with the set-up that you have in the file are Year and Month (on the slicers). No filter on Day and therefore MIN(dCalendar[Date]) will actually be the first day in the selected month and MAX(dCalendar[Date]) the last day, as you intended.
Conclusion: it all comes down to the filter context the measure is under at time of execution.
Hope this is useful as it took some time to put together :smileyhappy:
Anonymous
Here you have a slightly modified version of your file in which I have placed your measures in the table visual that you already had (highlighted in colors). You can then see the results they yield on their own step by step, before finally getting to the infinite. This is a good method to try to find where the problem is whenever you come across issues like the one you had here.
I've also included the alternative coding mentioned before.
Cheers
PS: I see you have all the measures grouped in an empty table. Interesting. Can you explain how you did that? I think I have an idea but want to make sure. Obrigado
Hi AlB !
Thanks for your help and time. Now it's clear for me ...
Of course I can tell you about the measures grouped...
1. First insert an empty table in "Enter Data" (e.g. name: _Measures) then;
2. Insert at least one measure within this table then to the icon appear as "measures icon" hide and unhide the "Fields" tab in ">" .
Greetings from Brazil !