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.
Hello,
Need help for Calculating Cumulative Column as shown in above table.
I want to calculate the Moving sum depending upon the fiscal year as well as month.
Suppose if there is Apr-16 then I want the count as it is( ie, 5 in Cumulative Count.)
if it is May then I should get the count as (Apr-16+May-16) ( ie, (5+10)=15 in Cumulative Count.) and so on till Jan-17.
And if there is Apr-17 then I want the count as it is( ie, 65 in Cumulative Count.) for than I dont need sum for all the previous month it should show me as it is.
if it is May then I should get the count as (Apr-17+May-17) ( ie, (65+70)=135 in Cumulative Count.) and so on till Jan-18.
Solved! Go to Solution.
HI @PrachiD
Try this solution
First a Calculated Column for Month Numbers
Month Number = IF ( MONTH ( TableName[Year-Month] ) > 3, MONTH ( TableName[Year-Month] ) - 3, MONTH ( TableName[Year-Month] ) + 9 )
Now a column for Years
YEAR = IF ( MONTH ( TableName[Year-Month] ) <= 3, YEAR ( TableName[Year-Month] ) - 1, YEAR ( TableName[Year-Month] ) )
Now you can get Cumulative as follows
Cumulative = CALCULATE ( SUM ( TableName[Count] ), FILTER ( ALL ( TableName ), TableName[YEAR] = EARLIER ( TableName[YEAR] ) && TableName[Month Number] <= EARLIER ( TableName[Month Number] ) ) )
Thank you so much.
Can u please explain the Month Number Query why you have taken > 3 ,+9 like that
Hi @PrachiD
Becasue your Month starts from April which is the 4th month by default
To make April 1 from 4 we have to subtract 3
To make Jan 10 from 1 we have to add 9
Alternatively you could use this to get the Month numbers
Month_Number = SWITCH ( FORMAT ( TableName[Year-Month], "MMMM" ), "January", 10, "February", 11, "March", 12, "April", 1, "May", 2, "June", 3, "July", 4, "August", 5, "September", 6, "October", 7, "November", 8, "December", 9 )
or alternatively this
Month_Number = SWITCH ( MONTH ( TableName[Year-Month] ), 1, 10, 2, 11, 3, 12, 4, 1, 5, 2, 6, 3, 7, 4, 8, 5, 9, 6, 10, 7, 11, 8, 12, 9 )
As per Your Solution I got the answer but on the real time senario the I m facing many problems.
I m having 3 tables with me HeadCount table , Attrition table and Date table.
HeadCount and Attrition table are connected to Date table by month-year.
HeadCount and Attrition table are not connected to each other.
Now for Headcount table and Attrition Table I want to calculate the Cumulative Sum.
That Cumulative logic is as per you told in the above reply.
I want to calculate the % which include sum of cumulative from attrition table and sum of cumulative from HeadCount table.
formula we are writing in Date table Because we does not have relationship between HeadCount and Attrition table and formula is:-
%=(Cumlative(Released)/Cumlative(HeadCount))*100
As per your logic we have derived Calculated Column but it does not work when we are taking percentage
Use this formula in the Date Table
% = RELATED ( AttritionTable[Cumulative] ) / RELATED ( HeadCountTable[Cumulative] )
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |