Calculate column with subtract rows

[Guyakobov]

HI,

I want to calculate the column and subtract the rows.

the columns are this http://prntscr.com/o8ldj1 

and it should be like this:  http://prntscr.com/o8lfm5

Thanks

[MitchM]

I am a little confused by your screenshots as your expected table seems to have the dates sorted in the wrong direction. If the SUM's should go in order (ie, 2/1/18 = 1/1/18 + 2/1/18) this will do the job:

Rolling Start = 
CALCULATE(
    SUM( Table1[Floating Start] ),
    ALL( Table1[Start Date] ),
    FILTER( Table1, ( Table1[Start Date] ) <=  EARLIER( Table1[Start Date] ))
)

[Guyakobov]

HI,

The start date is all the months in the year 2018.

I want to create a measure - not a column.

the calc in the second screenshot explains how it should be. (2/1/18 = the second calc in the pic)

Regards 

[MitchM · Accepted solution]

Ok, this should work. It is a measure as well. Your title was a bit confusing as you asked for a calculate column.

FloatingStart = 
VAR SelectedYear = YEAR( SELECTEDVALUE( Table1[Start Date] ) )
VAR SelectedMonth = MONTH( SELECTEDVALUE( Table1[Start Date] ))
VAR FloatingStart =
    CALCULATE( 
        SUM( Table1[Floating Start] ),
        ALL( Table1 ),
        YEAR( Table1[Start Date] ) = SelectedYear,
        MONTH( Table1[Start Date] ) <= 13 - SelectedMonth
        )
RETURN
    FloatingStart

[Guyakobov]

HI,

if I wish to make it dynamic via date slicer?

http://prntscr.com/o8xebj

Regards 

[MitchM]

I think removing the ALL statement should do the trick.

FloatingStart = 
VAR SelectedYear = YEAR( SELECTEDVALUE( Table1[Start Date] ) )
VAR SelectedMonth = MONTH( SELECTEDVALUE( Table1[Start Date] ))
VAR Month_1 =
    CALCULATE( 
        SUM( Table1[Floating Start] ),        
        YEAR( Table1[Start Date] ) = SelectedYear,
        MONTH( Table1[Start Date] ) <= 13 - SelectedMonth
        )
RETURN
    Month_1

[Guyakobov]

HI,

I try it and http://prntscr.com/o9331u

Regards 

[MitchM]

It's working correctly, at least in regards to how I understood what you wanted. Your equation was laid out like:

January = 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 + 11 + 12

Feb = 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 + 11 

and so on.

Therefore, if you have an incomplete year the base amount is potentially going to be the same. As an example, if you are missing Dec you would have the same base for Jan and Fec (b/c 12 would essentially = 0).

Am I interpreting what you want correctly?

[Guyakobov]

HI,

what you said was close.

I created a cohort visual via matrix using columns ( 1,2,3,4,5.....) and rows (first of each month as you can see in the pic attach http://prntscr.com/o9ed9r .

what I want is to create a measure that ill use in a different visual that will sum up only the first column.

the first value should sum up all the 1 column, the second value should sum up the first column subtract the last value,

the third value should sum up the 1 column minus 2 rows and goes on...

[MitchM]

This may work. It requires making 2 measures where ASC_Rank is used to make Sum_Value work. I only had time to test it breifly, but it appears it works dynamically when start date is filtered. Let me know if it works

ASC_Rank = 
VAR ASC_Rank =
    RANKX(
        ALLSELECTED( Table1 ),
        CALCULATE( SELECTEDVALUE( Table1[Start Date] ) ),
        , ASC, Dense
    )
RETURN
    ASC_Rank

SUM_Value = 
VAR SelectedDesc =
    RANKX(
        ALLSELECTED( Table1 ),
        CALCULATE( SELECTEDVALUE( Table1[Start Date] ) ),
        , DESC, Dense
    )
RETURN    
    CALCULATE( 
        SUM(Table1[Floating Start] ),        
        FILTER( ALLSELECTED( Table1 ), SelectedDesc >= [ASC_Rank] )
    )

[Guyakobov]

HI,

When you wrote Floating start you ment this calc: 

FloatingStart = 
VAR SelectedYear = YEAR( SELECTEDVALUE( Table1[Start Date] ) )
VAR SelectedMonth = MONTH( SELECTEDVALUE( Table1[Start Date] ))
VAR Month_1 =
    CALCULATE( 
        SUM( Table1[Floating Start] ),        
        YEAR( Table1[Start Date] ) = SelectedYear,
        MONTH( Table1[Start Date] ) <= 13 - SelectedMonth
        )
RETURN
    Month_1

 if so, i tried this and i got only one value : http://prntscr.com/oaje7j

[MitchM]

Not at home so I can’t confirm, but I am pretty sure that is referencing the column ( I think that is what you called it in your very first screenshot)

[Guyakobov]

amazing! work perfectly! 

now i need a sum calc of each column that i could add it to the visual attach and show the 2 measures so i can divide them by each other : http://prntscr.com/oal8oa

in the end, it should be in one visual this calc:  http://prntscr.com/oalbtw