Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
See the result below and I have drawned the result I would like:
Solved! Go to Solution.
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.
@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,
KellyDid I answer your question? Mark my post as a solution!
Of course.
Her is an Excel where I try to explain what I like:
Basically what I like is the
Employee Change% = if(not(ISBLANK([Last Period Employee])),CALCULATE( (divide([Current Employees],[Last Period Employee]) -1)*100))
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.
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:
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
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.
@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,
KellyDid 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 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,
KellyDid 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.
Maybe Open Tickets? https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147
@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.
Thank you! I will look into it and see if it will solve the issue.
@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
@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
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.
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |