cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
udian Member
Member

Missing Last Day of Month in Line Chart

Hi,

 

I'm trying to display current Vs previos month volume in the same line chart (X axis - Days ofmonth, Y axis - Volume).

 

When the current month has less days than the previous month the X axis display only the days for the current month, causing the previous month's volume data to disappear.

 

For example, if i'm comparing October Vs November then the X axis ends with 30 although October also has 31.

 

How can i avoid this issue?

 

Thanks!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User
Super User

Re: Missing Last Day of Month in Line Chart

The issue is related to how filter context works.  Your visual is is providing the initial filter context and is applying a filter with the number of days in the current month but in your case this is preventing you seeing days that don't appear in the previous month (as the initial filter context has filtered them out).  There are probably a few ways to solve this,  

  • I would create a new table containing the day numbers 1 through 31 and load it into the model (called Days).  Do not connect this table to anything else in the model.  
  • Then replace the days on the chart with the numbers from this new table.  Initially it will give you the wrong answers as the new days will be part of the filter context however no filtering is occurring becuase there is no relationships.
  • Then write the following 2 measures that pick up the filtering from the disconnected table (These are adventureworks samples but you just need to alter for your data).

Current Month Sales = CALCULATE([Total Sales],filter('Calendar','Calendar'[DayNumberOfMonth] = max(Days[Day])))

Prior Month Sales = CALCULATE([Current Month Sales],DATEADD('Calendar'[Date],-1,MONTH))

 

With these formulas, the initial filter context no longer filters out the days that don't exist in the current month and hence it should do what you want.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Super User
Super User

Re: Missing Last Day of Month in Line Chart

Yes, you woudn't be able to use inbuilt time intelligence.  But you can create a manual version with a simple change to what I posted above (note the less than )

 

Current Month Sales MTD = CALCULATE([Total Sales],filter('Calendar','Calendar'[DayNumberOfMonth] <= max(Days[Day])))

 

One of the problems with the inbuilt time intelligence functions is they are simply syntax sugar - there is a more complex function sitting below that you don't need to know about and you can use the simple version.  This is all well and good for standard use cases, but if you want to do anything more complex then you need the more detailed formula.  But the easy versions have prevented you from actually learning how it works - catch 22.  🙂



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

12 REPLIES 12
Super User
Super User

Re: Missing Last Day of Month in Line Chart

What formula are you using in your measure?



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
udian Member
Member

Re: Missing Last Day of Month in Line Chart

Hi,

 

MTD Volume Amount USD - Prev Month:=TOTALMTD([Volume Amount USD],DATEADD(Dim_TimeTable[Date],-1,month))

Community Support Team
Community Support Team

Re: Missing Last Day of Month in Line Chart

Hi @udian,

 

You can try to use below formula if it works on your side.

 

Formula:

 

MTD Volume Amount USD - Prev Month = 
var currDate=MAX(Dim_TimeTable[Date])
var MonthDays= DAY(DATE(YEAR(currDate),MONTH(currDate)+1,1)-1)
return
TOTALMTD([Volume Amount USD],DATEADD(Dim_TimeTable[Date],-1*MonthDays,DAY))

 


Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Baskar Super Contributor
Super Contributor

Re: Missing Last Day of Month in Line Chart

Hey dude ,

 

try this one 

 

MTD Volume Amount USD - Prev Month:=calculate([Volume Amount USD],PREVIOUSMONTH(Dim_TimeTable[Date]))

 

let me know if it not helping u 

udian Member
Member

Re: Missing Last Day of Month in Line Chart

Hey,

 

Your formula is not good for me becauese i need an accumulativ measure so that i can use it in a line chart.

 

If i use your formula and place it in a line chart i get a flat line.

 

Thanks!

udian Member
Member

Re: Missing Last Day of Month in Line Chart

Hi,

 

Your formula causes all measure values to move one day back.

 

For example on the 30 the measure will display the value of the 31, but the x axis still display only 30 days.

 

Thanks!

Highlighted
Super User
Super User

Re: Missing Last Day of Month in Line Chart

The issue is related to how filter context works.  Your visual is is providing the initial filter context and is applying a filter with the number of days in the current month but in your case this is preventing you seeing days that don't appear in the previous month (as the initial filter context has filtered them out).  There are probably a few ways to solve this,  

  • I would create a new table containing the day numbers 1 through 31 and load it into the model (called Days).  Do not connect this table to anything else in the model.  
  • Then replace the days on the chart with the numbers from this new table.  Initially it will give you the wrong answers as the new days will be part of the filter context however no filtering is occurring becuase there is no relationships.
  • Then write the following 2 measures that pick up the filtering from the disconnected table (These are adventureworks samples but you just need to alter for your data).

Current Month Sales = CALCULATE([Total Sales],filter('Calendar','Calendar'[DayNumberOfMonth] = max(Days[Day])))

Prior Month Sales = CALCULATE([Current Month Sales],DATEADD('Calendar'[Date],-1,MONTH))

 

With these formulas, the initial filter context no longer filters out the days that don't exist in the current month and hence it should do what you want.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

udian Member
Member

Re: Missing Last Day of Month in Line Chart

Thanks for the detailed explanation!

 

wouldn't changing the initial filter context prevent me from using TOTALMTD function as i am no longer filtering on the Time dimension? 

 

Thanks!

Super User
Super User

Re: Missing Last Day of Month in Line Chart

Yes, you woudn't be able to use inbuilt time intelligence.  But you can create a manual version with a simple change to what I posted above (note the less than )

 

Current Month Sales MTD = CALCULATE([Total Sales],filter('Calendar','Calendar'[DayNumberOfMonth] <= max(Days[Day])))

 

One of the problems with the inbuilt time intelligence functions is they are simply syntax sugar - there is a more complex function sitting below that you don't need to know about and you can use the simple version.  This is all well and good for standard use cases, but if you want to do anything more complex then you need the more detailed formula.  But the easy versions have prevented you from actually learning how it works - catch 22.  🙂



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 169 members 2,063 guests
Please welcome our newest community members: