CODEWITHSUNDEEP
excelpowerpivotdaxcumulative-sum
Cleland
Cleland

Reputation: 359

Power pivot running total issue

Not sure if anyone can help me with a DAX issue I'm having. I've followed the advice on this blog (http://javierguillen.wordpress.com/2012/11/28/running-total-techniques-in-dax/). My measures are in the same format as this:

CALCULATE(     SUM( FactSales[SalesAmount] ),
    FILTER(
        ALL( DimDate) ,
        DimDate[Datekey] <= MAX( DimDate[Datekey] )
    )
)

I have two measures: a running target and running actuals. I want the actuals line to stop at the current date rather than flat lining from current date to the end of the year.

Does anyone know how I can achieve this?

Thanks for your help in advance.

Tom

Upvotes: 1

Views: 340

Answers (1)

Abhijeet Nagre
Abhijeet Nagre

Reputation: 916

Following correction would solve your problem. Replace first parameter to Calculate
From "SUM( FactSales[SalesAmount]"
with "IF( MAX(DimDate[DateKey]) > TODAY(),Blank(),SUM( FactSales[SalesAmount])".

=CALCULATE( IF( MAX(DimDate[DateKey]) > TODAY(),Blank(),SUM( FactSales[SalesAmount]),
    FILTER(
        ALL( DimDate) ,
        DimDate[DateKey] <= Max( DimDate[DateKey] )
    )
)

Upvotes: 1

Related Questions