Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Apologies for the awkward title, I am struggling to work out how to do this or even word the problem. I have a feeling it should be fairly straightforward but I am hitting a roadblock.
Pre-Launch Contacts = VAR ProductYW = FIRSTNONBLANK('Product Activity'[YW],0) RETURN CALCULATE( SUM('CS Data'[No of Contacts Answered]), FILTER('CS Data','CS Data'[YW] < ProductYW)
Solved! Go to Solution.
Hi @GregCet ,
I have copy paste some lines on the contacts and changed the year this gave me the folllowing values per week:
The cumulative values are the ones until that week (excluding current week) this table was made only making the use of the contacts table now making use of the measure I have sent earlier:
Pre-Launch Contacts =
VAR ProductYW = FIRSTNONBLANK('Product Activity'[YW],0)
RETURN
CALCULATE(
SUM('CS Data'[Contacts Answered]),
FILTER(ALL('CS Data'[YW]),'CS Data'[YW] < ProductYW))
You can see that when you cross filter this with the YW of the product dates you get the results you need.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @GregCet ,
Don't know how you are presenting the information in your report but believe this is related with the type of filtering you are doing, that can be impacted by other context (filters, columns, values).
Try the following change to your formula:
Pre-Launch Contacts =
VAR ProductYW = FIRSTNONBLANK('Product Activity'[YW],0)
RETURN
CALCULATE(
SUM('CS Data'[No of Contacts Answered]),
FILTER(ALL('CS Data''CS Data'[YW]),'CS Data'[YW] < ProductYW)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi Miguel,
Thanks for your suggestion.
The syntax didn't seem to work when copying in your solution. I tried to change it to this but this produces the same results as before:
Pre-Launch Contacts =
VAR ProductYW = FIRSTNONBLANK('Product Activity'[YW],0)
RETURN
CALCULATE(
SUM('CS Data'[No of Contacts Answered]),
FILTER(
ALL('CS Data'),
'CS Data'[YW] < ProductYW
)
)
Hi @GregCet
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks.
You can access two sample data files here. One for the contacts and one for the product activities:
https://drive.google.com/drive/folders/194g6y77zCW6Bs-4M2hidmSpzD_rn1HQW?usp=sharing
Please let me know if this does not work.
Hi @GregCet ,
I tried to create a table visual which apply your measure [Pre-Launch Contacts] and the field [YW] of table Product Activity on it base on your provided excel data, it can return the result just as shown in below screenshot.
How did you set the visual? Did you put the field [YW] of table Product Activity on your visual?
Best Regards
Hello @v-yiruan-msft ,
Thanks for your support, I really appreciate it.
The visual I am trying to create is as follows:
As I said, I can get this to work when making a measure which looks at the sum of contacts that is EQUAL to the launch week but not a less than or greater than.
Hi @GregCet .
First of all looking at your data you have an issue with the YW column in the Product activity you have the week 8 and the value is 20218 and for the week 10 forward you will have with 202120 so has you can see the size of the number are different so you can have difficulties comparing for example products launched in the first 9 weeks of a year with the previous values.
For this you need to have the YW with 6 digits so it would be 202108.
I have a question regarding the date of the launch in this case your lower date is 23rd february in week 8, however your contacts table show information starting on week 17 how are you identifying the product launch?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi Miguel,
Thanks for the response. I have amended the Year-Week columns in each table (of my actual PBIX file, not the sample) and this has not solved the issue unfortunately. To be clear, I padded the week number so that it would display as 6 digits (e.g. 202108 or 202006).
I did also realise after creating the sample that there was not much data which matches with the product launches. I only included a small sample since I am working with data on a weekly basis going back to the beginning of January 2019. I have now amended the sample file for product activities.
Hi @GregCet ,
Still not understanding what is the launch date your product activity starts on week 17 and yuor first contact is also on the week 17 so no contacts before the first activity.
Sorry for making this questions but I'm not understanding the model to what you consider the week launch of the product that is the point that you need to compare to make all the counts that you need.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI had made a bit of an error in the sample files provided as I limited some of the data which was available.
In my real world file, I have weekly data which goes back to January 2019 (201901) and so the measure should be summing all contacts made in the weeks from 201901 until the product launch date (e.g. 202117).
As I have said before, the week comparisons work when I use a = but if try to find a sum of contacts before (<) or after (>) then i do not get any results.
Hi @GregCet ,
I have copy paste some lines on the contacts and changed the year this gave me the folllowing values per week:
The cumulative values are the ones until that week (excluding current week) this table was made only making the use of the contacts table now making use of the measure I have sent earlier:
Pre-Launch Contacts =
VAR ProductYW = FIRSTNONBLANK('Product Activity'[YW],0)
RETURN
CALCULATE(
SUM('CS Data'[Contacts Answered]),
FILTER(ALL('CS Data'[YW]),'CS Data'[YW] < ProductYW))
You can see that when you cross filter this with the YW of the product dates you get the results you need.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you for all of your time and effort Miguel! Turns out I had the same measure as you have in your example solution and it turns out that I had a stray relationship connection which was not necessary but was messing up the measure showing the correct result (for some reason).
Thank you again!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |