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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

CALCULATE() with measure as a date filter

Hi all,

 

I'm using a measure to find the last date of a row in table (table1) already filtered by a date segment. Looks like this:

 

Last _date =
CALCULATE(LASTDATE(Table1[date]);    #find the last date available in table1
DATESINPERIOD('CALENDAR'[Date];LASTDATE('CALENDAR'[Date]);-7;DAY))  #define the range of dates 
 
Until here I know it's working properly because when I visualize the data I have the last date for all my items.
 
Item1 | Last_date_1
Item2 | Last_date_2
Item2 | Last_date_2
....
 
Now I want to add a an other measure to this visual. This is were I'm lost.
 
I would like to use my last_date measure as a date filter in a new CALCULATE() function.
 
Something like this but it's not working saying that a CALCULATE function has been used in a filter..
 
Volume_measure=CALCULATE(SUM(Table1[Volume]);Table1[date]=[Last_date])
 
Any idea, trick?
 
Thank you
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Problem solved with VAR

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

As requested by few people, the code should look like this

 

New_measure = 

VAR

variable=[last_date] #last_date is a measure

RETURN

CALCULATE(AVERAGE(Table1[volume];Table1[date]=variable)

 

last_date= 

CALCULATE(LASTDATE(Table1[date]);    #find the last date available in table1
DATESINPERIOD('CALENDAR'[Date];LASTDATE('CALENDAR'[Date]);-7;DAY))  #define the range of dates 

 

@SingSong 

 

Anonymous
Not applicable

Problem solved with VAR

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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