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
udian
Helper III
Helper III

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

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

12 REPLIES 12

What formula are you using in your measure?



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

Hi,

 

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

Baskar
Resident Rockstar
Resident Rockstar

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 

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!

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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!

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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!

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

I used the following measures on my model:

 

MTD Volume Amount USD:=CALCULATE([Volume Amount USD],FILTER(Dim_TimeTable,Dim_TimeTable[Day Of Month] <= max(Dim_Month_Days[MonthDay])))


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

 

Instead of accumulative measures the line chart displayed the volume amount for each day.

Which Day data do you have on your chart axis?  Do you have the day column from the disconnected table I mentioned?

 

Here is mine.

 

mtd.png



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

Thanks for all your help Matt.

 

I forgot to replace the X axis on my chart with the disconnected dimension. It works great now!

 

Thanks Again!

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.