topic Re: Date filter in Calculate function in DAX Commands and Tips

Date filter in Calculate function

joseluis1969 — Thu, 04 Jul 2019 16:02:47 GMT

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

Re: Date filter in Calculate function

avanderschilden — Thu, 04 Jul 2019 17:29:31 GMT

Hello,

What about this?

Measure =

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

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

Re: Date filter in Calculate function

joseluis1969 — Fri, 05 Jul 2019 08:27:03 GMT

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:

Re: Date filter in Calculate function

avanderschilden — Fri, 05 Jul 2019 08:52:47 GMT

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

Re: Date filter in Calculate function

Anonymous — Fri, 05 Jul 2019 08:54:37 GMT

"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

Re: Date filter in Calculate function

answeriver — Thu, 23 Apr 2020 07:52:01 GMT

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

Re: Date filter in Calculate function

Anonymous — Wed, 22 Jun 2022 18:54:29 GMT

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.