cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

Thanks in advance,

José Luis

 

1 ACCEPTED SOLUTION

Accepted Solutions
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

View solution in original post

5 REPLIES 5
Highlighted
Resolver I
Resolver I

Re: Date filter in Calculate function

Hello,

 

What about this?

 

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?Any Ideas?

Highlighted
Resolver I
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] ) ) )

Capture.PNG
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

View solution in original post

Highlighted
Helper IV
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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors