cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper III

## Max Week in Month

Hello All!

Hoping someone can help!

I have data set like this.. each week there is a new snapshot taken. In a matrix I am showing the number of users by location by month. As you can see in the snapshot for 2/11/2021 we have 7 users, 1 in Germany and 6 in Denmark. Problem is what I want to show is the values for the last week in that month. Currently what i get is the output in the first table i.e. 1 in Germany and 1 in Denmark, whereas what I want is the position as of 16/11/2021... the measure needs to be able to always calculate based on the last week of data for that month... so when we add a snapshot for the following week, 23/11/2021, it would use this in instead.

I have a date table.

Cheers

Andy

1 ACCEPTED SOLUTION
Community Support

Hi, @andybamber ;

IF you create a column , try it.

``````No. Country =
Var _MW = CALCULATE(max(Res[Week Of Month]),ALLEXCEPT(Res,'Res'[Month]))
return
IF([Week Of Month]=MAXX(FILTER(ALL('Res'),[Month]=EARLIER(Res[Month])),[Week Of Month]),1)``````

The final output is shown below:

Or you could create a measure.

``````No. Country_m =
Var _MW = CALCULATE(max(Res[Week Of Month]),ALLEXCEPT(Res,'Res'[Month]))
return
CALCULATE(COUNT(Res[z_country_key]),FILTER(ALL('Res'),EOMONTH([snaphot_stamp],0)=EOMONTH(MAX('Date_table'[Date]),0)&&[Week Of Month]=MAXX(FILTER(ALL('Res'),[Month]=MAX([Month])),[Week Of Month])&&[z_country_key]=MAX('Res'[z_country_key])))``````

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 REPLIES 8
Community Support

Hi, @andybamber ;

IF you create a column , try it.

``````No. Country =
Var _MW = CALCULATE(max(Res[Week Of Month]),ALLEXCEPT(Res,'Res'[Month]))
return
IF([Week Of Month]=MAXX(FILTER(ALL('Res'),[Month]=EARLIER(Res[Month])),[Week Of Month]),1)``````

The final output is shown below:

Or you could create a measure.

``````No. Country_m =
Var _MW = CALCULATE(max(Res[Week Of Month]),ALLEXCEPT(Res,'Res'[Month]))
return
CALCULATE(COUNT(Res[z_country_key]),FILTER(ALL('Res'),EOMONTH([snaphot_stamp],0)=EOMONTH(MAX('Date_table'[Date]),0)&&[Week Of Month]=MAXX(FILTER(ALL('Res'),[Month]=MAX([Month])),[Week Of Month])&&[z_country_key]=MAX('Res'[z_country_key])))``````

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper III

Hi There!

Both work, i think column is better as that gives me totals in the matrix... I had also requested some help with the year issue, but i have now solved that by turning month into a monthyear value... seems to work!

I do have one further request though, which I didnt consider at the time, and that is where data spans over years... how can this be acheived? I presume i need to include a year attribute somewhere?

Cheers

Andy

Super User

Can you post sample data as text and expected output?
Not enough information to go on;

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Helper III

Hi there! so this is the sample data... the data is joined to a date table on snapshot date

 Snapshot Date User ID Country 02/11/2021 a1 Germany 02/11/2021 a2 Denmark 02/11/2021 a3 Denmark 02/11/2021 a4 Denmark 02/11/2021 a5 Denmark 02/11/2021 a6 Denmark 02/11/2021 a7 Denmark 09/11/2021 a2 Denmark 09/11/2021 a3 Denmark 09/11/2021 a4 Denmark 09/11/2021 a5 Denmark 09/11/2021 a6 Denmark 09/11/2021 a7 Denmark 16/11/2021 a2 Denmark 16/11/2021 a3 Denmark 16/11/2021 a4 Denmark 16/11/2021 a5 Denmark 16/11/2021 a6 Denmark 16/11/2021 a7 Denmark

Output wise, the matrix should show for Nov 21 the following results:

 Nov 2021 Denmark 6

This based on the latest week in November so far being 16/11/21

At the moment what I get is:

 Nov 2021 Germany 1 Denmark 6

Which is the 6 for Denmark from 16/11/21 and the 1 from Germany from 2/11/21... i dont want to include any results from 2/11/21 or 9/11/21... just the latest week of the month

Hope this helps

Cheers

Andy

Super User

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Helper III

Hello!

I worked through your solution, but all i end up with is the same numbers for every country, I think you need a more realistic set of data from me, so please see this PBI in link.. this has multiple tables and relationships along with the date table... if you look at November 21 for instance you will see a line for 'missing data' this entry has a snaphot date of 2/11/21, and so shouldnt be included... If i apply your methodology then it doesnt seem to work due to the other relationships, im presuming the data table may be the issue

Thanks again

Andy

Helper III

1

Helper III

1

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Fabric Community Update - April 2024

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

#### Power BI Monthly Update - March 2024

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

Top Solution Authors
Top Kudoed Authors