Cumulative Summary filtered on start date and end date

[Anonymous]

I have a value column am1, a start date and end date column, and a calculated column Cumulative_Portfolio

For each row I wish to store the cumulative sum of am1 in the column Cumulative_Portfolio.

This is what I have done so far, but it only gives the sum for that row and not for that row pluss all the ones before minus the ones with end date lower than that rows start date.

Cumulative_Portfolio =

CALCULATE(

SUM('VB Portefølje'[Am1]);

FILTER(

VALUES('VB Portefølje'[Start_Date])

;'VB Portefølje'[Start_Date] <= MAX('VB Portefølje'[Start_Date]) && 'VB Portefølje'[RNo] < 1000

)

;FILTER(

VALUES('VB Portefølje'[End_Date])

;ISBLANK('VB Portefølje'[End_Date]) || 'VB Portefølje'[End_Date] >= 'VB Portefølje'[End_Date] && 'VB Portefølje'[RNo] < 1000

)

)

See the result below and I have drawned the result I would like:

[v-kelly-msft]

Hi @Anonymous ，

Can you provide me some sample data with expected output?You can upload it to onedrive business and share the link with us,which can make your requirement more clear.

Do remember to remove the confidential information.

Much appreciated.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

---

@v-kelly-msft wrote:

Hi @Anonymous ，

Can you provide me some sample data with expected output?You can upload it to onedrive business and share the link with us,which can make your requirement more clear.

Do remember to remove the confidential information.

Much appreciated.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

---

Of course.

Her is an Excel where I try to explain what I like:

Example1 

Basically what I like is the

Employee Change% = if(not(ISBLANK([Last Period Employee])),CALCULATE( (divide([Current Employees],[Last Period Employee]) -1)*100))

Active Employee example 

Showing current employees except I like to show current proftfolio, am1, at any given time. F.eks. a graph showing the trend over time by month, quarter or year. Basically, how big was our portfolio in january 2019 and how big was it in January 2020.

I hope i am being clear enough.

I also need to filter it by RNo being between 1-1000, but that is the easy part. 🙂

Here is the desensitivize pbix file. Example1.pbix 

I did however notice by desensitiviing the file my date table when corrupt whic could be an answer to why I could not get it to work earlier by the other example.

[Anonymous]

---

I did however notice by desensitiviing the file my date table when corrupt whic could be an answer to why I could not get it to work earlier by the other example.

---

I have corrected the date table to:

Date =

VAR MinYear = YEAR ( MIN ( 'VB Portefølje'[Start_Date] ) )

VAR MaxYear = YEAR ( MAX ( 'VB Portefølje'[Start_Date] ) )

RETURN

ADDCOLUMNS (

CALENDAR(MinYear;MaxYear);

"Calendar Year"; "CY " & YEAR ( [Date] );

"Month Name"; FORMAT ( [Date]; "mmmm" );

"Month Number"; MONTH ( [Date] );

"Month Year";FORMAT([Date];"mmm") & "-" & YEAR([Date]);

"Month Year Sort";YEAR([Date]) & FORMAT([Date];"mm")

)

[Anonymous]

Also turns out my relations were wrong. I had based them on the orignal field for date and not the calculated field. I will try now and see if it works better.

Edit:

It still gives me circular error when I create:

Terminated Contracts = CALCULATE( COUNT( 'VB Portefølje'[RNo] ) ;USERELATIONSHIP( 'VB Portefølje'[End_Date];'Date'[Date]) ;NOT( ISBLANK( 'VB Portefølje'[End_Date] ) ) )

but not for:

Signed Contracts = CALCULATE( COUNT( 'VB Portefølje'[RNo] ) ;USERELATIONSHIP( 'VB Portefølje'[Start_Date];'Date'[Date] ) )

Edit2:'

Updated Example file

Example 1 

[v-kelly-msft]

Hi @Anonymous ,

After checking your data,I feel it's a bit difficult to get the result,so can you just keep column AM1 ,start data,RNo and end date with your expected output to make your requirement easy to understand?

Much appreciated.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

---

@v-kelly-msft wrote:

Hi @Anonymous ,

After checking your data,I feel it's a bit difficult to get the result,so can you just keep column AM1 ,start data,RNo and end date with your expected output to make your requirement easy to understand?

Much appreciated.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

---

I am not sure if I understand you correctly, what Start data? Is there anything else than the xlsx or pbix file you need? The xlsx file contains RNo, Start Date, End Date, am1 and example of my desired result.

[Anonymous]

---

@Anonymous wrote:

---

@v-kelly-msft wrote:

Hi @Anonymous ,

After checking your data,I feel it's a bit difficult to get the result,so can you just keep column AM1 ,start data,RNo and end date with your expected output to make your requirement easy to understand?

Much appreciated.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

---

I am not sure if I understand you correctly, what Start data? Is there anything else than the xlsx or pbix file you need? The xlsx file contains RNo, Start Date, End Date, am1 and example of my desired result.

---

I think I have solved it by looking into the examples given in HR Analytics, and solved the circular refereance by switching from Column to Meassure, as described here Understanding Circular Dependances and here, Circular Dependance and tweaking the datetable. I will let you know.

[Anonymous · Accepted solution]

OK!

Worked it out! 🙂

Here is the final result: Example 

And a picture of what I wanted:

The code for the red line is the cumulative portfolioThe red line is the cumulative portfolio

[Greg_Deckler]

Maybe Open Tickets? https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

[Anonymous]

---

@Greg_Deckler wrote:

Maybe Open Tickets? https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

---

I tested this but this is just a more complex way of doing what I did, but using COUNT instead of SUM. 🙂 The result is the same. It only shows the ones in a specificly new in a time period were as I need the cumulative in that period.

[Anonymous]

Thank you! I will look into it and see if it will solve the issue.

[amitchandak]

@Anonymous , not sure I got it. But if you looking find all active between start date and end date, month-wise  or day wise, refer this blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

---

@amitchandak wrote:

@Anonymous, not sure I got it. But if you looking find all active between start date and end date, month-wise  or day wise, refer this blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

---

I am getting either circle error or User relationship error when I try to apply this to my own dataset. I will look into the example pbix and see if there is something fundmentally different.