Running Totals - Graph Question (graphing items per business day of the month)

[CSMcVey]

I am using PowerPivot to generate a graph. The graph will display a running total of amounts per business day. I have 4 lines (series) on the graph, one for the current month and previous 3 months.

I have it working, with one exception. If my data was through the 7th business day of November - I have the Nov line climbing up to the 7th business day but then my life is flat for the remainder of the business days. I understand why it's doing this - when I look @ the underlying table the value remains static for the rest of the business days. What I would really like is for the line to stop @ this point. Also, Nov only has 20 business days, it should never extend to business day 21,22 or 23 but it does b/c a previous month (Oct) has these values.

Any suggestions?

 

[Laurent C]

You can try to enclose your measure in an IF Expression
=IF( COUNTROWS(Data);
CALCULATE([YourMeasure];
DATESMTD(Dates[Date])
)
;BLANK())

 

[CSMcVey]

Laurent:

This is working w/ one small exception. I have pasted a table below.

My data includes data up through the 3rd business day of the month for January 2013. What I would like is for the 4th business day on for Jan 13 those rows would return blank (just like the 22 & 23 business day do for December).

The idea is I don't want dates to calculate that are greater than the latest day in my data. The problem is when I graph the table below, I get a large horizontal line @ business day 3 for Jan 13 and would rather the line stop @ that point.





[TABLE="width: 490"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Testing[/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]2012 - 10 - Oct[/TD]
[TD]2012 - 11 - Nov[/TD]
[TD]2012 - 12 - Dec[/TD]
[TD]2013 - 01 - Jan[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]1130[/TD]
[TD="align: right"]1071[/TD]
[TD="align: right"]1243[/TD]
[TD="align: right"]982[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]2291[/TD]
[TD="align: right"]2290[/TD]
[TD="align: right"]2528[/TD]
[TD="align: right"]2070[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]3521[/TD]
[TD="align: right"]3394[/TD]
[TD="align: right"]3830[/TD]
[TD="align: right"]3226[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]4699[/TD]
[TD="align: right"]4443[/TD]
[TD="align: right"]5098[/TD]
[TD="align: right"]3226[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]5779[/TD]
[TD="align: right"]5637[/TD]
[TD="align: right"]6235[/TD]
[TD="align: right"]3226[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]6749[/TD]
[TD="align: right"]6831[/TD]
[TD="align: right"]7353[/TD]
[TD="align: right"]3226[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]7886[/TD]
[TD="align: right"]8026[/TD]
[TD="align: right"]8681[/TD]
[TD="align: right"]3226[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]9043[/TD]
[TD="align: right"]8805[/TD]
[TD="align: right"]9890[/TD]
[TD="align: right"]3226[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]10247[/TD]
[TD="align: right"]9915[/TD]
[TD="align: right"]11041[/TD]
[TD="align: right"]3226[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]11289[/TD]
[TD="align: right"]11121[/TD]
[TD="align: right"]12118[/TD]
[TD="align: right"]3226[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD="align: right"]12490[/TD]
[TD="align: right"]12203[/TD]
[TD="align: right"]13097[/TD]
[TD="align: right"]3226[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD="align: right"]13626[/TD]
[TD="align: right"]13351[/TD]
[TD="align: right"]14246[/TD]
[TD="align: right"]3226[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD="align: right"]14750[/TD]
[TD="align: right"]14462[/TD]
[TD="align: right"]15364[/TD]
[TD="align: right"]3226[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD="align: right"]15997[/TD]
[TD="align: right"]15633[/TD]
[TD="align: right"]16350[/TD]
[TD="align: right"]3226[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD="align: right"]17039[/TD]
[TD="align: right"]16603[/TD]
[TD="align: right"]17211[/TD]
[TD="align: right"]3226[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD="align: right"]18182[/TD]
[TD="align: right"]16720[/TD]
[TD="align: right"]17542[/TD]
[TD="align: right"]3226[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD="align: right"]19395[/TD]
[TD="align: right"]17818[/TD]
[TD="align: right"]18422[/TD]
[TD="align: right"]3226[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD="align: right"]20560[/TD]
[TD="align: right"]19114[/TD]
[TD="align: right"]19390[/TD]
[TD="align: right"]3226[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD="align: right"]21730[/TD]
[TD="align: right"]20281[/TD]
[TD="align: right"]20459[/TD]
[TD="align: right"]3226[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD="align: right"]22714[/TD]
[TD="align: right"]21519[/TD]
[TD="align: right"]21222[/TD]
[TD="align: right"]3226[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD="align: right"]23784[/TD]
[TD="align: right"]22584[/TD]
[TD][/TD]
[TD="align: right"]3226[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD="align: right"]24893[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD="align: right"]25885[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Laurent C]

By nesting the calculation within an IF( COUNTROWS(Data); ...), I get no figures past the 3rd business day (in January). It would be nice, if you could provide a sample of your data for your time and data tables.

However, I realize my suggestion will not display any value either, when no data is available for a specific day.

Instead of using COUNTROWS(Data), checking whether there are data past the "current" date, would be preferrable.

= IF(
     MAX( Dates[Date]) 
     <= CALCULATE( MAX(Data[Date]); 
            ALLEXCEPT(Data; Dates[Month])
   );
     TOTALMTD( [Your Measure]; Dates[Date]);
     BLANK()
  )