Forum Discussion

andybamber's avatar
andybamber
Helper III
4 years ago
Solved

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 sna...
Help appreciated
Max date from select dates in Dataset
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

VahidDM's avatar
VahidDM
Super User
4 years ago

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's avatar
andybamber
Helper III
4 years ago

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

    • andybamber's avatar
      andybamber
      Helper III
      4 years ago

      1

      • andybamber's avatar
        andybamber
        Helper III
        4 years ago

        1

    • andybamber's avatar
      andybamber
      Helper III
      4 years ago

      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