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  

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi AlB

 

Thank you for the help so fast. :smileyvery-happy:

Could you tell me why the code I have posted did not work? Just to understand the reason ... 

 

Tks

AlB's avatar
AlB
Community Champion
7 years ago

Anonymous

:smileyhappy: You're welcome.

I could, if you share the pbix. Otherwise we'd have to spend too much time while I ask about details omitted in your explanation.

In any case, my current guess would be as follows:

Fact: the division yields infinite,

so [Interval Days] must be zero,

which means [Initial Day] = [Last Day],

which implies that MIN(dCalendario[Data]) = MAX(dCalendario[Data]),

from which we can deduce that the day number for dCalendario[Data] has a sole distinct value in the filter context when the measure is evaluated.

 

You have Month and Day in the rows of your visual. That defines a sole day number value in dCalendario[Data].

     

 

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Anonymous

     

    Was I clear enough?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi AlB !

       

      Sorry for the delay in reply, I was traveling on the weekend.

       

      Thank you, I understood your explanation, but I do not understand why the interval of days on the card is normally displayed, but when I use it in a measure the interval considered is zero. : smileyfrustrated:
      Ok I'll check to share the PBIX here ... 

       

      Greetings !

      • AlB's avatar
        AlB
        Community Champion
        7 years ago

        Hi Anonymous

         

        You have a table visual with Year, Month and Day, all columns of table dCalendar. All three fields will be creating a filter context for the measures you include later in the table visual, right? So when you include for instance [First Day]

         

        First Day =
CALCULATE (
    SELECTEDVALUE ( dCalendar[Date].[Dia] );
    FILTER ( ALL ( dCalendar ); dCalendar[Date] = MIN ( dCalendar[Date] ) )
)

         

         every row in the table visual has a filter on one day only. Let's take one row as an example, you have:

        Year=2018, Month=1, Day=1

        so your dCalendar[Date] column is filtered down to that day only, 1st of January, 2018. MIN(dCalendar[Date]) is therefore that day: 2018/01/01. The result of your FILTER( ) operation is dCalendar with only one row, the one containing  2018/01/01.  Then SELECTEDVALUE(... ) returns the day in that only row, which is 1. Bear in mind that you only had one day value to start with. so:

         

         

        DAY ( SELECTEDVALUE ( dcalendar[Date] ) )

         would yield exactly the same result as your code.