Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
andybamber
Helper III
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.

snapshot.PNG

 

Cheers

Andy

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
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:

vyalanwumsft_0-1638424463292.png

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:

vyalanwumsft_1-1638424504717.png

 

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.

View solution in original post

8 REPLIES 8
v-yalanwu-msft
Community Support
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:

vyalanwumsft_0-1638424463292.png

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:

vyalanwumsft_1-1638424504717.png

 

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.

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
Super User
Super User

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/

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

 

Snapshot DateUser IDCountry
02/11/2021a1Germany
02/11/2021a2Denmark
02/11/2021a3Denmark
02/11/2021a4Denmark
02/11/2021a5Denmark
02/11/2021a6Denmark
02/11/2021a7Denmark
09/11/2021a2Denmark
09/11/2021a3Denmark
09/11/2021a4Denmark
09/11/2021a5Denmark
09/11/2021a6Denmark
09/11/2021a7Denmark
16/11/2021a2Denmark
16/11/2021a3Denmark
16/11/2021a4Denmark
16/11/2021a5Denmark
16/11/2021a6Denmark
16/11/2021a7Denmark

 

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

 

 Nov 2021
Denmark6

 

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

At the moment what I get is:

 

 Nov 2021
Germany1
Denmark6

 

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

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/

 

 

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

1

1

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors