Return Max Week number based on number of days

[bmk]

Below image is two columns from a Date table, it shows Week number and Date.

How do I return the max week number for the last period with 7 days?

For example, the week number that should be returned from the below scenario is week 34.

[Jihwan_Kim · Accepted solution]

Hi,

First of all, thank you for your message via Linkedin.

I justed checked the message, and please check the below measure and the attached pbix file.

I tried to create a sample pbix file like below, and I hope the below can provide some ideas on how to create a solution for your dataset.

Expected result measure: =
VAR _weektable =
    FILTER (
        ADDCOLUMNS (
            VALUES ( Data[Week of Year] ),
            "@dayscountinaweek", CALCULATE ( COUNTROWS ( Data ) )
        ),
        [@dayscountinaweek] = 7
    )
RETURN
    MAXX ( _weektable, Data[Week of Year] )

[bmk]

The measure did not work. Also, noticed that when I use the measure, it filters the date range to the last 8 days (example, when a relative date of last 14 days is selected) only.

Measure : 

Measure = MAXX(FILTER('Date Table', 'Date Table'[Date] >= TODAY()-7 && 'Date Table'[Date] <= TODAY()), 'Date Table'[WeekNum])

As per image below, measure should still return 34. Since week 35 only has four days complete so far.

[v-yiruan-msft]

Hi @bmk ,

According to your description, You want to get the maximum Weeknum for the full week .Right?

(1)This is my test data:

(2)We can create a measure,then it can meet your need: “Max_Weeknum”

Max_Weeknum = MAXX( FILTER('Test', WEEKDAY('Test'[Date],2)=7 ) , 'Test'[Weeknum])

(3)The result is as follows:

If this method does not meet your needs, you can provide us your sample data without sensitive data, or detailed input and output sample data, so that we can better help for you.

Best Regards

[amitchandak]

@bmk ,  Try a measure like

Maxx(filter(Table, Table[Date] >= Today() -7  && Table[Date]  <= today()) , [Week])