cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gary5 Frequent Visitor
Frequent Visitor

Calculate Cumulative count and reset every year

Hello all,

 

I have a Table called 'YTD' with columns like Emp_ID, Last_Work_Day(LWD)

I got the count of Total leavers and Cumulative Leavers by below derivations.

I did like this  : 

Total leavers = CALCULATE(COUNTA('YTD'[Emp_ID]),'YTD'[LWD]<>BLANK(), USERELATIONSHIP('YTD'[LWD],'Calendar'[Date]))
and 
Cumulative Leavers = CALCULATE(COUNTA('YTD'[Emp_ID]), DATESBETWEEN('Calendar'[Date],MINX(ALL('Calendar'[Date]),'Calendar'[Date]),MAX('Calendar'[Date])),USERELATIONSHIP('YTD'[LWD],'Calendar'[Date]))

 

I've data on few years, and I'd like to reset my Cumulative Leavers on every January,

I tried few things but couldn't help. Can you please guide on this.

 

 

 

image.png

Thank you in advance for your help ! 

1 ACCEPTED SOLUTION

Accepted Solutions
tex628 New Contributor
New Contributor

Re: Calculate Cumulative count and reset every year

Cumulative Leavers = 
Var Year_ = Selectedvalue('Calendar'[Year])
Return
CALCULATE(COUNTA('YTD'[Emp_ID]), DATESBETWEEN('Calendar'[Date],MINX(ALL('Calendar'[Date]),'Calendar'[Date]),MAX('Calendar'[Date])),USERELATIONSHIP('YTD'[LWD],'Calendar'[Date]); 'Calendar'[Year] = Year_)
2 REPLIES 2
tex628 New Contributor
New Contributor

Re: Calculate Cumulative count and reset every year

Cumulative Leavers = 
Var Year_ = Selectedvalue('Calendar'[Year])
Return
CALCULATE(COUNTA('YTD'[Emp_ID]), DATESBETWEEN('Calendar'[Date],MINX(ALL('Calendar'[Date]),'Calendar'[Date]),MAX('Calendar'[Date])),USERELATIONSHIP('YTD'[LWD],'Calendar'[Date]); 'Calendar'[Year] = Year_)
gary5 Frequent Visitor
Frequent Visitor

Re: Calculate Cumulative count and reset every year

Thank you @tex628 so much!!

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 171 members 2,235 guests
Please welcome our newest community members: