Forum Discussion

joseluis1969's avatar
joseluis1969
Frequent Visitor
7 years ago
Solved

Date filter in Calculate function

Good afternoon: I am trying to make a measure that reflects in a table the total cost Budgeted by day, month and year ONLY UNTIL the last date of the "Real" table. I have (among others) the followin...
  • Anonymous's avatar
    Anonymous
    7 years ago

    "The question is: With what dax expression can I substitute VALUE (28/06/2019) for the FINAL DATE of the "Real" table ? (28/06/2019 is the last date of the "Real" table)."

     

    Well, that's rather simple:

    [Very Last Date in Real] :=
calculate(
    max( Real[Date] ),
    all( Real )
)

    and here's how you'd use it:

    var __lastDateInReal = [Last Date in Real]
return
calculate (
	sum ( Ppto[importe] );
	KEEPFILTERS( Calendario[Date] <= __lastDateInReal )
)

    if you want to intersect filters on Calendario that are coming from the outer context... and this is probably what you want to do.

     

    Best

    Darek

avanderschilden's avatar
avanderschilden
Resolver I
7 years ago

Hello,

 

What about this?

 

Measure =

VAR MaxDateBalance = MAX('Table'[Date])

RETURN CALCULATE(SUM('Table'[Column]),FILTER(ALL('Date'),'Date'[Date]<MaxDateBalance))
  • joseluis1969's avatar
    joseluis1969
    Frequent Visitor
    7 years ago

    Hello:

    Thank you very much for your answer. The measurement you indicate does not work correctly, I try to explain it with the following image:

    Any Ideas?

    • avanderschilden's avatar
      avanderschilden
      Resolver I
      7 years ago

      Not sure if I understand you correctly, but the following measure is also displaying values for periods that don't exisit in the actuals table;

       

      Cumulative Actual Amount =

      CALCULATE ( SUM ( Actual[Actual_Amount] ) ,
      FILTER ( ALL ( 'Calendar' ) , 'Calendar' [Date] <= MAX ( 'Calendar'[Date] ) ) )