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