Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Days calculating error

Hi.    I'm trying to make a calculation based on the difference between two dates (two days), but the result is not the one that is expected (in red), but shows as "infinite".  It seems to me that...
Need Help
  • AlB's avatar
    AlB
    7 years ago

    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]
    )

     

    Code formatted with  

AlB's avatar
AlB
Community Champion
7 years ago

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:

 

 

 

               

AlB's avatar
AlB
Community Champion
7 years ago

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  

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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 !