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

Anonymous's avatar
Anonymous
Not applicable
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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hello, Thank you for the help.

In my case, I want to get the max date depending on the filter and go back a year.

For example, if the selected date on the Real date filter is 6/22/2022. I want to filter another customer table where customer date equals 6/22/2021.