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.
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
Solved! Go to 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
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] )) )
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
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
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
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?
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...
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] ) )
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
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
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |