Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Agnessia
New Member

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:

 

Screenshot (1).png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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
Anonymous
Not applicable

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:

date table.png

 

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.  
    • DatesInPeriod no sales.png
  • This is why when you filter the dates to a date that had sales, you get a Total
  • dates in period sales.png

 

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:

all the same.png

Just something to be aware of.  

 

I hope that helps clear it up (and not make it worseSmiley Frustrated.  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

View solution in original post

5 REPLIES 5
themistoklis
Community Champion
Community Champion

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

 

 

Anonymous
Not applicable

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?

 

 

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...

Anonymous
Not applicable

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:

date table.png

 

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.  
    • DatesInPeriod no sales.png
  • This is why when you filter the dates to a date that had sales, you get a Total
  • dates in period sales.png

 

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:

all the same.png

Just something to be aware of.  

 

I hope that helps clear it up (and not make it worseSmiley Frustrated.  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

Thank you so much for all the details @Anonymous! 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! Smiley Very Happy

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.