Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I am having scenario wherein we need to determine the previous year however the issue is the previous year might be a non-consecutive year. Let take an example:-
Year Activity Value
1/1/2021 A 5
1/1/2020 A 3
1/1/2017 A 2
1/1/2015 A 6
1/1/2020 B 4
1/1/2018 B 2
1/1/2017 B 2
1/1/2019 C 5
1/1/2017 C 2
1/1/2016 C 5
1/1/2020 D 3
1/1/2016 D 2
1/1/2013 D 1
Result expected:- 1/1/2020 - A - 3
1/1/2018 - B - 2
1/1/2017 - C - 2
1/1/2016 - D - 2
Any help will be highly appreciated.
Hi, @Diptarup
Please check the below picture and the sample pbix file, whether it is what you are looking for.
all measures are in the sample pbix file.
https://www.dropbox.com/s/37jg6zt34zwl5f7/diptarup.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Its possible to show only the Aggregate score to this such as 9
Hi, @Diptarup
Thank you for your feedback.
Is it 8 or 9?
If 8 is correct, I fixed my measure like below.
And the link is down below.
https://www.dropbox.com/s/37jg6zt34zwl5f7/diptarup.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
no answer should be 9 (A 3 + B 2+ C2 +D2)=(3+2+2+2=9)
Hi, @Diptarup
Sorry for my mistake.
I was not careful to add numbers.
Please check the below.
This has to be done in two steps in order to write measures more in an understandable way.
https://www.dropbox.com/s/37jg6zt34zwl5f7/diptarup.pbix?dl=0
If you still cannot open the link, please let me konw.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Many thanks for the help. There is one condition that I forgot to mention in the example is if the Activity is done for only one time then that value should not get added to the result. To taking the previous example
Year Activity Value
1/1/2021 A 5
1/1/2020 A 3
1/1/2017 A 2
1/1/2015 A 6
1/1/2020 B 4
1/1/2018 B 2
1/1/2017 B 2
1/1/2019 C 5
1/1/2017 C 2
1/1/2016 C 5
1/1/2020 D 3
1/1/2016 D 2
1/1/2013 D 1
1/1/2020 E 5
1/1/2018 F 7
the overall scores should still be 9. Apologies for not mentioning it earlier. This is the last part that is remaining.
Many thanks for taking out time and helping me. I am still unable to access the link. Can you please attach the pbix file in this page itself.
Hi, @Diptarup
I cannot attach the file here. Honestly, I do not know how to attach the file directly here..
I will try to use googledrive, and please check the below.
If still does not work, please send me a message with your email address, then I will send an email with the attachment.
https://drive.google.com/file/d/1gCHTqXJ0XhLGVJAvxnWU7RTUzK3rYduW/view?usp=sharing
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Many thanks for all the help so far. I did forget to mention if an activity occurred only once in that case we need to exclude them from calculation. Taking the above example
i have added E and F under actvity which has occurred once and hence computiting the overall score they should be omitted. The Overall number should still be 9. Again apologies for not highlighting it earlier.
Year Activity Value
1/1/2021 A 5
1/1/2020 A 3
1/1/2017 A 2
1/1/2015 A 6
1/1/2020 B 4
1/1/2018 B 2
1/1/2017 B 2
1/1/2019 C 5
1/1/2017 C 2
1/1/2016 C 5
1/1/2020 D 3
1/1/2016 D 2
1/1/2013 D 1
1/1/2020 E 5
1/1/2015 F 7
Hi, @Diptarup
Thank you for your message.
Now, I am not sure if I wrote my measure correctly.
Please try to insert your new sample into the table, and try my measure.
It will still show the same result.
The reason is that I use < lastnonblankvalue function + filters less than the max year > in the measure.
It means,
only one year = no last year value = no result value
Please kindly check and please let me have your advice.
Thank you.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Many thanks for the answer. Can you please attached the pbix file here. Unable to access your dropbox link. Also it is possible to show the total instead of year seggregation
Hey @Diptarup ,
I don't really understand your case? How is the algorithm that only the results A3, B2 , C2 and D2 are being returned? In this case you didn't do any transformation, the year is still the same number.
What should happen with A5, A2 and A6?
I think you need to give some more explanation in order to help you.
I only need previous year data to reflect they might be non - consecutive years.
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |