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
Solved! Go to Solution.
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.
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.
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
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/
Hi there! so this is the sample data... the data is joined to a date table on snapshot date
Snapshot Date | User ID | Country |
02/11/2021 | a1 | Germany |
02/11/2021 | a2 | Denmark |
02/11/2021 | a3 | Denmark |
02/11/2021 | a4 | Denmark |
02/11/2021 | a5 | Denmark |
02/11/2021 | a6 | Denmark |
02/11/2021 | a7 | Denmark |
09/11/2021 | a2 | Denmark |
09/11/2021 | a3 | Denmark |
09/11/2021 | a4 | Denmark |
09/11/2021 | a5 | Denmark |
09/11/2021 | a6 | Denmark |
09/11/2021 | a7 | Denmark |
16/11/2021 | a2 | Denmark |
16/11/2021 | a3 | Denmark |
16/11/2021 | a4 | Denmark |
16/11/2021 | a5 | Denmark |
16/11/2021 | a6 | Denmark |
16/11/2021 | a7 | Denmark |
Output wise, the matrix should show for Nov 21 the following results:
Nov 2021 | |
Denmark | 6 |
This based on the latest week in November so far being 16/11/21
At the moment what I get is:
Nov 2021 | |
Germany | 1 |
Denmark | 6 |
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
HI @andybamber
Download this file: https://gofile.io/d/BuMMOO
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
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
1
1
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!