- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Days calculating error

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

aBerg

Regular Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-25-2019
03:51 AM

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 the interval between days (Interval Days measure) despite being shown correctly on the card (30 days) for the calculation is considering as zero, so the result as "infinite".

Could someone help me by pointing out a way to solve this error ?

Greetings !

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

AlB

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-25-2019
07:21 AM

Hi @aBerg

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

11 REPLIES 11

AlB

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-25-2019
07:21 AM

Hi @aBerg

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

aBerg

Regular Visitor

Re: Days calculating error

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-25-2019
08:40 AM

Hi @AlB

Thank you for the help so fast.

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

Tks

AlB

Super User

Re: Days calculating error

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-25-2019
09:16 AM

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

Super User

Re: Days calculating error

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-25-2019
01:07 PM

aBerg

Regular Visitor

Re: Days calculating error

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-28-2019
05:07 AM

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 !

aBerg

Regular Visitor

Re: Days calculating error

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-28-2019
07:21 AM

AlB

Super User

Re: Days calculating error

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-30-2019
02:57 AM

Hi @aBerg

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.

Highlighted
##

AlB

Super User

Re: Days calculating error

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-30-2019
03:06 AM

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

AlB

Super User

Re: Days calculating error

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-30-2019
03:12 AM

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