cancel
Showing results 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

New Member

## how to achieve avg of the columns

Hi.

I have calculated measure  from column E to J and A to D are the values from DB
(attached screenshot for quick view
and I required row no 14 to 17 ( Avg of 7D,30D,60D and MTD)
typo error --> its 60D and not 90D in the screenshot
)

1 ACCEPTED SOLUTION
Super User

Step 1: I make a calendar table.

Step 2: I add a relationship.

Step 3: I make these measures for the 'Start' column.

Last 7D AVG_Start = CALCULATE(AVERAGE('DATA'[Start]),DATESBETWEEN('Calendar'[Date],TODAY()-6,TODAY()))

Last 30D AVG_Start = CALCULATE(AVERAGE('DATA'[Start]),DATESBETWEEN('Calendar'[Date],TODAY()-29,TODAY()))

Last 60D AVG_Start = CALCULATE(AVERAGE('DATA'[Start]),DATESBETWEEN('Calendar'[Date],TODAY()-59,TODAY()))

MTD AVG_Start = CALCULATE(AVERAGE('DATA'[Start]),DATESBETWEEN('Calendar'[Date],DATE(YEAR(TODAY()),MONTH(TODAY()),1),TODAY()))

Step 4: I make other measures for other columns.

Super User

Step 1: I make a calendar table.

Step 2: I add a relationship.

Step 3: I make these measures for the 'Start' column.

Last 7D AVG_Start = CALCULATE(AVERAGE('DATA'[Start]),DATESBETWEEN('Calendar'[Date],TODAY()-6,TODAY()))

Last 30D AVG_Start = CALCULATE(AVERAGE('DATA'[Start]),DATESBETWEEN('Calendar'[Date],TODAY()-29,TODAY()))

Last 60D AVG_Start = CALCULATE(AVERAGE('DATA'[Start]),DATESBETWEEN('Calendar'[Date],TODAY()-59,TODAY()))

MTD AVG_Start = CALCULATE(AVERAGE('DATA'[Start]),DATESBETWEEN('Calendar'[Date],DATE(YEAR(TODAY()),MONTH(TODAY()),1),TODAY()))

Step 4: I make other measures for other columns.

Announcements

#### 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.