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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
arveseba
Frequent Visitor

Average value for last 7 day in other table

issue.PNGHi,

 

I need count in column Table2[Last7d] average Table[TotalProduct] value of the last 7 days ofr each employee.

I was looking solution on similar topics, but i didn't find it.

How does it look in DAX?

I tried: Last7d = CALCULATE(

AVERAGE(

Table1[TotalProduct]),

DATESINPERIOD(Table1[Date],

LASTDATE(Table1[Date]),-7,DAY))

 

but i have only results 1 and 0.

Where did I make mistake ?

1 ACCEPTED SOLUTION
Eric_Zhang
Employee
Employee


@arveseba wrote:

issue.PNGHi,

 

I need count in column Table2[Last7d] average Table[TotalProduct] value of the last 7 days ofr each employee.

I was looking solution on similar topics, but i didn't find it.

How does it look in DAX?

I tried: Last7d = CALCULATE(

AVERAGE(

Table1[TotalProduct]),

DATESINPERIOD(Table1[Date],

LASTDATE(Table1[Date]),-7,DAY))

 

but i have only results 1 and 0.

Where did I make mistake ?


@arveseba

For those functions DATEINPERIOD, LASTDATE, PREVIOUSMONTH etc, please use a consecutive calendar table. Then change the measure a little bit. The attached demo is for your reference.

Last7d =
CALCULATE (
    AVERAGE ( Table1[TotalProduct] ),
    DATESINPERIOD ( DateTable[Date], LASTDATE ( DateTable[Date] ), -7, DAY )
)

Capture.PNG

View solution in original post

1 REPLY 1
Eric_Zhang
Employee
Employee


@arveseba wrote:

issue.PNGHi,

 

I need count in column Table2[Last7d] average Table[TotalProduct] value of the last 7 days ofr each employee.

I was looking solution on similar topics, but i didn't find it.

How does it look in DAX?

I tried: Last7d = CALCULATE(

AVERAGE(

Table1[TotalProduct]),

DATESINPERIOD(Table1[Date],

LASTDATE(Table1[Date]),-7,DAY))

 

but i have only results 1 and 0.

Where did I make mistake ?


@arveseba

For those functions DATEINPERIOD, LASTDATE, PREVIOUSMONTH etc, please use a consecutive calendar table. Then change the measure a little bit. The attached demo is for your reference.

Last7d =
CALCULATE (
    AVERAGE ( Table1[TotalProduct] ),
    DATESINPERIOD ( DateTable[Date], LASTDATE ( DateTable[Date] ), -7, DAY )
)

Capture.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.