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.

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