Forum Discussion

alanmccarthy's avatar
alanmccarthy
Frequent Visitor
6 years ago
Solved

Measure flatlines after source column has no more data

I'm using measures to show the running total of costs, from the "Eur Equiv" column (formula below).
One issue is that the measure "flatlines" on its total after the current month (when there is no more values in the "Eur Equiv column"). 
Is it possible to stop the measure once the date goes past Now() ?
 
EUR Total - Actual =
CALCULATE (
SUM ( [EUR Equiv] ),
FILTER (
ALLselected ( 'Calendar'[Year/Month] ),
'Calendar'[Year/Month] <= MAX ( 'Calendar'[Year/Month] )
),'Budget Actual'[Budget,Actual]="Actual"
)
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    So generally you could do something like:

     

    EUR Total - Actual =
VAR __myCalc =
CALCULATE (
SUM ( [EUR Equiv] ),
FILTER (
ALLselected ( 'Calendar'[Year/Month] ),
'Calendar'[Year/Month] <= MAX ( 'Calendar'[Year/Month] )
),'Budget Actual'[Budget,Actual]="Actual"
)
RETURN
IF(MAX('Calendar'[Date])>NOW(),BLANK(),__myCalc)
  • Anonymous's avatar
    Anonymous
    6 years ago

    alanmccarthy 


    Try replace  'Calendar'[Year/Month] <= MAX ( 'Calendar'[Year/Month] ) to 'Calendar'[Year/Month] <=NOW()

     

    EUR Total - Actual = 
CALCULATE ( 
SUM ( [EUR Equiv] ),
FILTER (
ALLselected ( 'Calendar'[Year/Month] ),
'Calendar'[Year/Month] <=NOW()
),'Budget Actual'[Budget,Actual]="Actual"
)

     

     

    Paul Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    So generally you could do something like:

     

    EUR Total - Actual =
VAR __myCalc =
CALCULATE (
SUM ( [EUR Equiv] ),
FILTER (
ALLselected ( 'Calendar'[Year/Month] ),
'Calendar'[Year/Month] <= MAX ( 'Calendar'[Year/Month] )
),'Budget Actual'[Budget,Actual]="Actual"
)
RETURN
IF(MAX('Calendar'[Date])>NOW(),BLANK(),__myCalc)
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    alanmccarthy 


    Try replace  'Calendar'[Year/Month] <= MAX ( 'Calendar'[Year/Month] ) to 'Calendar'[Year/Month] <=NOW()

     

    EUR Total - Actual = 
CALCULATE ( 
SUM ( [EUR Equiv] ),
FILTER (
ALLselected ( 'Calendar'[Year/Month] ),
'Calendar'[Year/Month] <=NOW()
),'Budget Actual'[Budget,Actual]="Actual"
)

     

     

    Paul Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.