Running total only for Fact Table Date range

[mdrew9]

Calculate([SpecialCost],Datesbetween(DimDate[Date],FirstDate(All(DimDate[Date])),LastDate(DimDate[Date])))


This measure is in a pivot table filtered by DimDate[Year] and [MonthShortName].

It gives me a running total, but this "Special Cost" is actuals to date. If I have cost accrued December - March (in my Fact Table), I want March to show the total to date December - March. But April should show 0, graphically I want to have a line graph that stops after March because I do not have actuals. I think I need to use an All Except type of function. Any ideas?

 

[powerpivotpro]

That last argument to DATESBETWEEN() doesn't have to be a column in your Calendar table - you could use a MAX(<column in="" your="" fact="" table="">column in your fact table) there for instance. Does that help?</column>

 

[mdrew9]

It doesn't because DimDate April, nothing has changed in my measure I'm still summing between December and March. What I want is 0 in April.

 

[powerpivotpro]

I don't understand - where in your data is it defined that April should NOT count? What table/column would you look at to find, for instance, a max date of March 31? Is that in your fact table? If so I would think that changing your last DATESBETWEEN argument to reference that column instead of a DimDate column might help you.

 

[mdrew9]

In my Fact table, which holds the actuals. There is only 4 months of data Dec - March. In my pivot, April (DimDate) still shows a max(March 31), which still gives me my running total Dec-Mar. For example if each month I had $1 of actuals Dec - March, in March my sum would be $4, in DimDate April (as the pivot is organized) using Max(March 31) still gives me $4 total. But what I want is not to show anything in April. Dec - April should be $1,$2,$3,$4,$0 respectively.

 

[mdrew9]

Essentially if my DimDate > FactableDate then I want the output to be Blank.

If (DimDate is less than FacttableDate,Calculate([SpecialCost],Datesbetween(DimDate[Date],FirstDate(All(DimDate[Date])),LastDate(DimDate[Date]))),blank())

Thats kind of what I want, but doesn't work syntax may be incorrect.

 

[mdrew9]

I solved the the other day and I thought I would update my solution here.
=if(ISBLANK([SpecialCost]),blank(),Calculate([SpecialCost],Datesbetween(DimDate[Date],FirstDate(All(DimDate[Date])),LastDate(DimDate[Date])))