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

Helper I

## How to get the earliest week and latest week when fact and date table are both present

I have 1 Fact table and 1 Date table. I would like 2 new metrics.

Earliest week of =

Latest week of =

Both metrics should be based on the orders in the Fact table, not the Date table. I'm still learning.

Thanks

1 ACCEPTED SOLUTION
Super User

Try these measures:

``````Earliest Week Of =
VAR vMinFactDate =
CALCULATE ( MIN ( 'FACT'[DATE] ), ALL ( 'FACT' ) )
VAR vTable =
FILTER ( ALL ( 'DATE' ), 'DATE'[DATE] = vMinFactDate )
VAR vResult =
MAXX ( vTable, 'DATE'[WEEK OF] )
RETURN
vResult``````
``````Latest Week Of =
VAR vMaxFactDate =
CALCULATE ( MAX ( 'FACT'[DATE] ), ALL ( 'FACT' ) )
VAR vTable =
FILTER ( ALL ( 'DATE' ), 'DATE'[DATE] = vMaxFactDate )
VAR vResult =
MAXX ( vTable, 'DATE'[WEEK OF] )
RETURN
vResult``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Super User

Try these measures:

``````Earliest Week Of =
VAR vMinFactDate =
CALCULATE ( MIN ( 'FACT'[DATE] ), ALL ( 'FACT' ) )
VAR vTable =
FILTER ( ALL ( 'DATE' ), 'DATE'[DATE] = vMinFactDate )
VAR vResult =
MAXX ( vTable, 'DATE'[WEEK OF] )
RETURN
vResult``````
``````Latest Week Of =
VAR vMaxFactDate =
CALCULATE ( MAX ( 'FACT'[DATE] ), ALL ( 'FACT' ) )
VAR vTable =
FILTER ( ALL ( 'DATE' ), 'DATE'[DATE] = vMaxFactDate )
VAR vResult =
MAXX ( vTable, 'DATE'[WEEK OF] )
RETURN
vResult``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

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.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors