Need to create a calculated measure , from Date table ,where date table is imported from other model

[smitha_]

Need to create a calculated measure to display ,Latest completed week and all other weeks in a slicer, from Date table ,where date table is imported from other model.

Date table

Date ID (YYYYMMDD)	Calendar - Date	Calendar - Day of Week Number	DATE_ISO_DATE_FORMAT	Fiscal - Day Of The Year	Calendar - Day Of The Year	Calendar - Previous Day	Calendar - Same Date Last Week	Calendar - Same Date 2 Weeks Ago	Calendar - Same Date 3 Weeks Ago	Calendar - Same Date 4 Weeks Ago	Calendar - Same Date Last Year	Calendar - Same Date 2 Years Ago	Calendar - Same Date 3 Years Ago	Calendar - Same Date 4 Years Ago	Fiscal - Same Date in FY2019	Calendar - Days from Today	Calendar - Week ID (YYYYWW)	Calendar - Week Number	Calendar - Week of Month Number	Calendar - Weeks from Current Week	Calendar - Month ID (YYYYMM)	Calendar - Month Number	Calendar - Months from Current Month	Calendar - Quarter ID (YYYYQ)	Calendar - Quarter Number	Calendar - Quarter Display	Calendar - Quarters from Current Quarter	Calendar - Year Number	Calendar - Years from Current Year	Fiscal - Week ID (YYYYWW)	Fiscal - Week Number	Fiscal - Week Display	Fiscal - Week Short Display	Fiscal - Week Start Date	Fiscal - Week End Date	Fiscal - Week of Period Number	Fiscal - Weeks from Current Week	Fiscal - Period ID (YYYYPP)	Fiscal - Period Number	Fiscal - Period Name	Fiscal - Weeks in Period	Fiscal - Period Start Date	Fiscal - Period End Date	Fiscal - Periods from Current Period	Fiscal - Quarter ID (YYYYQ)	Fiscal - Quarter Number	Fiscal - Quarter Name	Fiscal - Quarter Short Name	Fiscal - Quarter Start Date	Fiscal - Quarter End Date	Fiscal - Quarters from Current Quarter	Fiscal - Half Number	Fiscal - Half Start Date	Fiscal - Half End Date	Fiscal - Year Number	Fiscal - Year Start Date	Fiscal - Year End Date	Fiscal - Years from Current Year	Calendar - Weekend Indicator	ETL_INSERT_TS	ETL_UPDATE_TS
20170131	1/31/2017	2	2017-01-31	2	31	1/30/2017	1/24/2017	1/17/2017	1/10/2017	1/3/2017	2/2/2016	2/3/2015	2/4/2014	2/5/2013	1/29/2019	-3106	201705	5	-46	-443	201701	1	-103	20171	1	Q1	-34	2017	-8	201701	1	Week 01	01	1/30/2017	2/5/2017	1	-443	201701	1	Period 01	4	1/30/2017	2/26/2017	-101	20171	1	Quarter 1	Q1	1/30/2017	4/30/2017	-33	1	1/30/2017	7/30/2017	2017	1/30/2017	1/28/2018	-8	N	1/16/2024 2:03:51 PM	8/3/2025 12:01:15 AM
20170201	2/1/2017	3	2017-02-01	3	32	1/31/2017	1/25/2017	1/18/2017	1/11/2017	1/4/2017	2/3/2016	2/4/2015	2/5/2014	2/6/2013	1/30/2019	-3105	201705	5	1	-443	201702	2	-102	20171	1	Q1	-34	2017	-8	201701	1	Week 01	01	1/30/2017	2/5/2017	1	-443	201701	1	Period 01	4	1/30/2017	2/26/2017	-101	20171	1	Quarter 1	Q1	1/30/2017	4/30/2017	-33	1	1/30/2017	7/30/2017	2017	1/30/2017	1/28/2018	-8	N	1/16/2024 2:03:51 PM	8/3/2025 12:01:15 AM
20170202	2/2/2017	4	2017-02-02	4	33	2/1/2017	1/26/2017	1/19/2017	1/12/2017	1/5/2017	2/4/2016	2/5/2015	2/6/2014	2/7/2013	1/31/2019	-3104	201705	5	1	-443	201702	2	-102	20171	1	Q1	-34	2017	-8	201701	1	Week 01	01	1/30/2017	2/5/2017	1	-443	201701	1	Period 01	4	1/30/2017	2/26/2017	-101	20171	1	Quarter 1	Q1	1/30/2017	4/30/2017	-33	1	1/30/2017	7/30/2017	2017	1/30/2017	1/28/2018	-8	N	1/16/2024 2:03:51 PM	8/3/2025 12:01:15 AM
20170203	2/3/2017	5	2017-02-03	5	34	2/2/2017	1/27/2017	1/20/2017	1/13/2017	1/6/2017	2/5/2016	2/6/2015	2/7/2014	2/8/2013	2/1/2019	-3103	201705	5	1	-443	201702	2	-102	20171	1	Q1	-34	2017	-8	201701	1	Week 01	01	1/30/2017	2/5/2017	1	-443	201701	1	Period 01	4	1/30/2017	2/26/2017	-101	20171	1	Quarter 1	Q1	1/30/2017	4/30/2017	-33	1	1/30/2017	7/30/2017	2017	1/30/2017	1/28/2018	-8	N	1/16/2024 2:03:51 PM	8/3/2025 12:01:15 AM
20170130	1/30/2017	1	2017-01-30	1	30	1/29/2017	1/23/2017	1/16/2017	1/9/2017	1/2/2017	2/1/2016	2/2/2015	2/3/2014	2/4/2013	1/28/2019	-3107	201705	5	-46	-443	201701	1	-103	20171	1	Q1	-34	2017	-8	201701	1	Week 01	01	1/30/2017	2/5/2017	1	-443	201701	1	Period 01	4	1/30/2017	2/26/2017	-101	20171	1	Quarter 1	Q1	1/30/2017	4/30/2017	-33	1	1/30/2017	7/30/2017	2017	1/30/2017	1/28/2018	-8	N	1/16/2024 2:03:51 PM	8/3/2025 12:01:15 AM

[v-pgoloju]

Hi @smitha_,

Just following up to see if the response provided was helpful in addressing the issue. If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[swathigouda]

hi @smitha_ hope this helps 

Slicer column =

VAR weekno = WEEKNUM('Calendar'[Date])

VAR max_weekno = MAX('Calendar'[Week of Year])

RETURN

IF(

    weekno < max_weekno,

    FORMAT('Calendar'[Year],"0000") & " week"  & FORMAT(weekno, "0"),

    "Latest week " & FORMAT(weekno, "0")

)

[v-pgoloju]

Hi @smitha_,

Just following up to see if the response provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

Best regards,

Prasanna Kumar

[v-pgoloju]

Hi @smitha_,

Just following up to see if the response provided was helpful in addressing the issue. If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[v-pgoloju · Accepted solution]

Hi @smitha_,

Thank you for reaching out to the Microsoft Fabric Forum Community.  Thanks to @MFelix for prompt and helpful response.

Based on my understanding of your requirements, I have provided the suggestions below. If I have misunderstood anything, or if you are still facing issues, please provide more context as suggested by @smitha_  so that we can assist you further.

I'm assuming, The Date table contains a [Calendar - Week ID (YYYYWW)] column to uniquely identify each week, a [Calendar - Week Number] column, and both [Calendar - Week Start Date] and [Calendar - Week End Date] columns to determine if a week is complete. It follows a standard date table structure with at least one row for each day. 

Create a measure to get week Id
Latest_Completed_Week_ID = 
CALCULATE(MAX('Date'[Calendar - Week ID (YYYYWW)]),
    FILTER('Date', 'Date'[Calendar - Week End Date] < TODAY()))

Create a calculated column to 
Week_Filter = 
VAR LatestWeek = CALCULATE(MAX('Date'[Calendar - Week ID (YYYYWW)]),
        FILTER( 'Date', 'Date'[Calendar - Week End Date] < TODAY()))
RETURN
IF('Date'[Calendar - Week ID (YYYYWW)] <= LatestWeek, "Show", "Hide")

Best regards,
Prasanna Kumar

[MFelix]

Hi @smitha_ ,

Can you give some more context on what you want to calculate and expected result?

If you are able to give some more context we are able to help you better.