How to find the previous year even though it is a non-consective one.

[Diptarup]

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.

[Jihwan_Kim]

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

[Diptarup]

Its possible to show only the Aggregate score to this such as 9

[Jihwan_Kim]

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 

Result value =

VAR currentactivity =

MAX ( 'Table'[Activity] )

VAR latestdate =

CALCULATE (

MAX ( 'Table'[Year] ),

FILTER ( ALL ( 'Table' ), 'Table'[Activity] = currentactivity )

)

VAR lastnonblankdatevalue =

CALCULATE (

LASTNONBLANKVALUE( 'Table'[Year], SUM ( 'Table'[Value] ) ),

FILTER (

ALL ( 'Table' ),

'Table'[Activity] = currentactivity

&& 'Table'[Year] < latestdate

)

)

RETURN

SUMX(VALUES('Table'[Activity]), lastnonblankdatevalue )

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

[Diptarup]

no answer should be 9 (A 3 + B 2+ C2 +D2)=(3+2+2+2=9)

[Jihwan_Kim]

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.

Result value  step 1=

VAR currentactivity =

MAX ( 'Table'[Activity] )

VAR latestdate =

CALCULATE (

MAX ( 'Table'[Year] ),

FILTER ( ALL ( 'Table' ), 'Table'[Activity] = currentactivity )

)

VAR lastnonblankdatevalue =

CALCULATE (

LASTNONBLANKVALUE( 'Table'[Year], SUM ( 'Table'[Value] ) ),

FILTER (

ALL ( 'Table' ),

'Table'[Activity] = currentactivity

&& 'Table'[Year] < latestdate

)

)

RETURN

IF( ISFILTERED('Table'[Activity]), lastnonblankdatevalue )

Result Value total Fix step 2=

SUMX(VALUES('Table'[Activity]), [Result value])

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

[Diptarup]

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.

[Diptarup]

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. 

[Jihwan_Kim]

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

[Diptarup]

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

[Jihwan_Kim]

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

[Diptarup]

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

[selimovd]

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.

Best regards

Denis

[Diptarup]

I only need previous year data to reflect they might be non - consecutive years.