cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## 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 following tables in my data model:
- Calendar
- Ppto: Table of costs budgeted from 01/01/2019 to 31/12/2019
- Real: Table of Actual Accounting costs from 01/01/2019 to 28/06/2019
The result that I intend to obtain must be equal to what the following measure would give me:

Total = calculate(sum(Ppto[importe]);filter(Calendario;Calendario[Date]<=VALUE(28/06/2019)))

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).

For example, if I use this measure: "calculate (sum (Ppto [importe); filter (Calendario; Calendario [Date] <= max (Real [date]))" the result I get is incorrect because in the table "Real" there are not all the dates that exist in the" Calendario "table.

José Luis

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Anonymous
Not applicable

## Re: Date filter in Calculate function

"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

5 REPLIES 5
Highlighted
Resolver I

## Re: Date filter in Calculate function

Hello,

Measure =

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

RETURN CALCULATE(SUM('Table'[Column]),FILTER(ALL('Date'),'Date'[Date]<MaxDateBalance))
Highlighted
Frequent Visitor

## Re: Date filter in Calculate function

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?

Highlighted
Resolver I

## Re: Date filter in Calculate function

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] ) ) )

Highlighted
Anonymous
Not applicable

## Re: Date filter in Calculate function

"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

Highlighted
Helper IV

## Re: Date filter in Calculate function

Hi all,

How i can use formula which help me make sameperiod last month.

Now i use this, but it's error (red text)

SPLM = CALCULATE([mln st];REPORT_IMS[VISIT YEAR]=YEAR(TODAY());'REPORT_IMS'[VISIT MONTH]=MONTH(TODAY())-1;REPORT_IMS[WORKING DAY]<=CALCULATE(MAX(REPORT_IMS[WORKING DAY];DATESMTD(REPORT_IMS[VISIT DATE]))))

How i can fix it?

Basically i need sales (mln st) in previous month in same count of working days in this month

Announcements

#### Microsoft Ignite

This will be a conference that you do not want to miss!

#### Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors