cancel
Showing results for
Did you mean:
Agnessia Frequent Visitor

Total doesn't show up for a measure

I'm exploring Power BI, and I seem to be stuck on a simple thing I can't figure out.

I built this simple table visual: For some reason, the total is displayed for DATESINBETWEEN column, but not for DATESINPERIOD. They are formatted the same way (decimal number, \$ currency). Here's the formulas for both of them:

DATESBETWEEN = CALCULATE([Sum_Sales],DATESBETWEEN(Dates[Date],STARTOFMONTH(Dates[Date]),ENDOFMONTH(Dates[Date])))

DATESINPERIOD = CALCULATE([Sum_Sales],DATESINPERIOD(Dates[Date],STARTOFMONTH(Dates[Date]),1,MONTH))

What I'm trying to do is to display the total for DATESINPERIOD. Could anybody please tell me what I'm doing wrong?

Thank you for your help!

1 ACCEPTED SOLUTION

Accepted Solutions Super User

Re: Total doesn't show up for a measure

I think I see what you are saying now.  First things first, your dates in period formula should be a -1  (maybe 1 with start of month could work, but never seen it that way so not sure)

Dates In Period 1 Month =

CALCULATE(
[Total Sales] ,
DATESINPERIOD(
'Dimension Date'[Date],
LASTDATE('Dimension Date'[Date]),
-1,
MONTH )
)
)

Second issue is to be sure that your Calendar table is set as a date table to utilize the time-intelligence functions correctly.

In the Data view --> Modeling--> Calendars--> Mark as Data table: Third thing to remember is that these time intelligence functions return a list of dates as a filter.

Then the issue with the totals is all about context and more specifically filter context in this situation.  The only filter we have here are the Dates coming from your table.  And why you only sometimes get a total DATESINPERIOD is because of that.  So what is the DatesInPeriod formula above really saying?

• CALCULATE means time to change the filter context (in this case dates)
• Ok, cool, so what are going to change and what are we going to change it to?
• In the current filter context, what is that date.  Not entirely sure in your example, but using World Wide Importers, the last date in the calendar is  12/31/16.  So the Total here has every date up to that.  If it was a total for a year, it would be just those dates in the year, or if by month it would be jus those dates in that month.
• Knowing that, when use LASTDATE at the Total it will take 12/31/16 and go back 1 MONTH from that, so 12/01/16.  It takes those list of dates and filters the Sales Table to compute the Total Sales measure.  There are no sales made then, so there is nothing to be returned.
• • This is why when you filter the dates to a date that had sales, you get a Total
• Hopefully that makes sense so far.  Now the next natural question is why does DATESBETWEEN always return a total?

Refreshed on what our formula is for that:

Dates Between 1 Month =
CALCULATE (
[Total Sales],
DATESBETWEEN (
'Dimension Date'[Date],
STARTOFMONTH ( 'Dimension Date'[Date] ),
ENDOFMONTH ( 'Dimension Date'[Date] )
)
)

So remember in Totals we are dealing with all the dates available in the current filter context.  So, as your table is set up now, the Total has the entire list of dates available.  So the function picks up the Start and the End of that list.  In the example above that list ended at 12/31/16 and began 1/1/13.  That's the list of dates that filters from the Calendar Table to the sales table, and that is what you see.  So while the end date (12/31/16) has no sales all all the other dates did, so you get a total.  That is also why you will see repeat values at the day level ( because as it is set up now you are telling the function to look at the current date of the fitler context and give the start and end of that date: Just something to be aware of.

I hope that helps clear it up (and not make it worse .  DAX is basically all about context.  Simple concept for sure, but there's nothing easy about it, but that's what it makes it fun!

-Nick

5 REPLIES 5 Super User

Re: Total doesn't show up for a measure

If this was working as you hoped, what would the total be there?  Big thing (if not the  biggest) is all about context.  DATESINPERIOD is going back 1 month from where you are in the current filter context (i.e. the date row in this case). Totals are just the absence of filters.  So what date would DAX use as the starting point for DATESINPERIOD?

Agnessia Frequent Visitor

Re: Total doesn't show up for a measure

The total for DATESINPERIOD would be the same as DATESBETWEEN. And my date ranges between July 4th 2015 and May 6th 2017 (first sales date is 07/04/15 and the last sales date is 05/06/17).

Also, if I change my formula to limit my range to July 4th 2015 to July 4th 2017, I get the correct total. So I guess I'm confused why I don't get the same total if I run a slightly different formula. I'm not sure what I'm missing to be honest... Super User

Re: Total doesn't show up for a measure

I think I see what you are saying now.  First things first, your dates in period formula should be a -1  (maybe 1 with start of month could work, but never seen it that way so not sure)

Dates In Period 1 Month =

CALCULATE(
[Total Sales] ,
DATESINPERIOD(
'Dimension Date'[Date],
LASTDATE('Dimension Date'[Date]),
-1,
MONTH )
)
)

Second issue is to be sure that your Calendar table is set as a date table to utilize the time-intelligence functions correctly.

In the Data view --> Modeling--> Calendars--> Mark as Data table: Third thing to remember is that these time intelligence functions return a list of dates as a filter.

Then the issue with the totals is all about context and more specifically filter context in this situation.  The only filter we have here are the Dates coming from your table.  And why you only sometimes get a total DATESINPERIOD is because of that.  So what is the DatesInPeriod formula above really saying?

• CALCULATE means time to change the filter context (in this case dates)
• Ok, cool, so what are going to change and what are we going to change it to?
• In the current filter context, what is that date.  Not entirely sure in your example, but using World Wide Importers, the last date in the calendar is  12/31/16.  So the Total here has every date up to that.  If it was a total for a year, it would be just those dates in the year, or if by month it would be jus those dates in that month.
• Knowing that, when use LASTDATE at the Total it will take 12/31/16 and go back 1 MONTH from that, so 12/01/16.  It takes those list of dates and filters the Sales Table to compute the Total Sales measure.  There are no sales made then, so there is nothing to be returned.
• • This is why when you filter the dates to a date that had sales, you get a Total
• Hopefully that makes sense so far.  Now the next natural question is why does DATESBETWEEN always return a total?

Refreshed on what our formula is for that:

Dates Between 1 Month =
CALCULATE (
[Total Sales],
DATESBETWEEN (
'Dimension Date'[Date],
STARTOFMONTH ( 'Dimension Date'[Date] ),
ENDOFMONTH ( 'Dimension Date'[Date] )
)
)

So remember in Totals we are dealing with all the dates available in the current filter context.  So, as your table is set up now, the Total has the entire list of dates available.  So the function picks up the Start and the End of that list.  In the example above that list ended at 12/31/16 and began 1/1/13.  That's the list of dates that filters from the Calendar Table to the sales table, and that is what you see.  So while the end date (12/31/16) has no sales all all the other dates did, so you get a total.  That is also why you will see repeat values at the day level ( because as it is set up now you are telling the function to look at the current date of the fitler context and give the start and end of that date: Just something to be aware of.

I hope that helps clear it up (and not make it worse .  DAX is basically all about context.  Simple concept for sure, but there's nothing easy about it, but that's what it makes it fun!

-Nick

themistoklis New Contributor

Re: Total doesn't show up for a measure

@Agnessia

Also totals wont be dsiplayed is there are null values in the cell.

You can use IFERROR function to convert them to 0.

DATESINPERIOD = CALCULATE(IFERROR([Sum_Sales].0),DATESINPERIOD(Dates[Date],STARTOFMONTH(Dates[Date]),1,MONTH))

Agnessia Frequent Visitor

Re: Total doesn't show up for a measure

Thank you so much for all the details @Nick_M! Your explanation made me realize something about DatesInPeriod function I didn't see before.

My problem was that I expected the same Total result from DatesInPeriod as from DatesBetween, but I didn't realize that DatesBetween is a finite function which gives a sum total, and DatesInPeriod is a continuous function that gives the last value as a total.

I've been following your steps, and when I tried filtering dates, it all made sense.

I greatly appreciate your help! Announcements Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge. Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform. Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Top Kudoed Authors
Users Online
Currently online: 2 members 311 guests
Recent signins:
• • jfd • Anil231 • Mathissimus • RenaudM • arnamq • sampson27 • LyssaEverix • Manojbagarwal 