Missing Last Day of Month in Line Chart

[udian]

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!

[MattAllington · Accepted solution]

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.

[MattAllington]

What formula are you using in your measure?

[udian]

Hi,

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

[Baskar]

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]

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!

[Anonymous]

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

[udian]

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!

[MattAllington · Accepted solution]

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.

[udian]

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!

[MattAllington · Accepted solution]

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

[udian]

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.

[MattAllington]

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.

[udian]

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!