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

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.

8 Replies

  • 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

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    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.

    • andybamber's avatar
      andybamber
      Helper III
      4 years ago

      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