Same period last year calculated column based on a condition

[tomislav_mi]

Hey guys,

I am struggling with how to get one challenging calculated column. I appreciate every help I can get.

This is my data set and the desired calculated column is in orange.

So, I am trying to compare Ending MRR of every customer and it's Ending MRR in the same period last year, and if the previous year Ending MRR is blank, I would need to get current Ending MRR.

Basically, I want to test is there any customer who was not active last year and is active now.

Customer	Report Date	Ending MRR	Same period last year MRR
A	2/28/2019	236
A	3/31/2019	236
A	4/30/2019	236
A	12/31/2019
B	6/30/2018
B	7/31/2018
B	8/31/2018
B	9/30/2018
B	10/31/2018
B	11/30/2018
B	12/31/2018
B	1/31/2019
B	2/28/2019
B	3/31/2019
B	4/30/2019
B	5/31/2019
B	6/30/2019
B	7/31/2019
B	8/31/2019	316	316
B	9/30/2019	316	316
B	10/31/2019	316	316
B	11/30/2019	316	316
B	12/31/2019	316	316
B	1/31/2020	316	316
B	2/29/2020	316	316

Any help is welcome.

Thanks!

[Icey · Accepted solution]

Hi @tomislav_mi ,

Try this:

Same period last year MRR = 
VAR MinDateofEachCustomer =
    MINX ( ALLEXCEPT ( 'Table', 'Table'[Customer] ), 'Table'[Report Date] )
VAR LastYearValue =
    CALCULATE (
        SUM ( 'Table'[Ending MRR] ),
        ALLEXCEPT ( 'Table', 'Table'[Customer] ),
        SAMEPERIODLASTYEAR ( 'Table'[Report Date] )
    )
RETURN
    IF (
        MinDateofEachCustomer < SAMEPERIODLASTYEAR ( 'Table'[Report Date] ),
        IF ( LastYearValue = BLANK (), 'Table'[Ending MRR], LastYearValue )
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tomislav_mi]

Thank you all guys, @Icey @amitchandak @Mariusz 

when I combined all I have read I got sollution!

Thanks all!

[amitchandak]

Something like this

if ( isblank(CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],0,Year)) ),
CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],0,Year))

CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year)))

[Mariusz]

Hi @tomislav_mi 

try SAMEPERIODLASTYEAR() function.
https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[tomislav_mi]

Hey @Mariusz ,

already tried to use it withing CALCULATE but it does not work. I could get limited solution when using it as a measure but not as a calculated column.

[Mariusz]

Hi @tomislav_mi 

When using a column add ALLEXCEPT( yourtable, yourtable[customerColumn ) to you CALCULATE as an extra argument 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn