Max Week in Month

[andybamber]

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

[v-yalanwu-msft · Accepted solution]

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.

[andybamber]

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

[VahidDM]

Hi @andybamber 

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

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

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.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

[andybamber]

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

[VahidDM]

HI @andybamber 

Download this file: https://gofile.io/d/BuMMOO

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[andybamber]

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

https://www.dropbox.com/s/pyd5gy0u3g4z8kj/PBI%20Problem.pbix?dl=0 

Thanks again

Andy

[andybamber]

1

[andybamber]

1