Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
joseluis1969
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
Anonymous
Not applicable

"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

6 REPLIES 6
Anonymous
Not applicable

"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
Not applicable

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.

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
avanderschilden
Resolver I
Resolver I

Hello,

 

What about this?

 

Measure =

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

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

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?

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.