Forum Discussion

andybamber's avatar
andybamber
Icon for Helper III rankHelper 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.

andybamber's avatar
andybamber
Icon for Helper III rankHelper 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