Forum Discussion
calculate sum between two dates
Hi All ,
I want to calculate the running total of license quntity between start_date_updated and expried date in a bar chart where i show the data in month wise .
two date fields are inactive releationship with master calender as shown below
I tried writing DAX logic but i m facing ambiguity in relationships.
Please help with DAX logic .
13 Replies
- amitchandak
Super User
Anonymous , Seem like you need active count, refer if these can help
Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU
In case you need cumulative of active
you can try like
Cumm Sales = CALCULATE(SUMX(values(Date[Date]),[Active Measure]),filter(allselected(date),date[date] <=max(date[Date])))
Hi,
We need to calculate running total of license qty between start and end date . Where both the dates are inactive releationship with master calender as shown in my post
Below is the data
Licence Qty Start _date Expired _date 148,361 1/1/2021 12/31/2021 802 1/28/2021 12/31/2021 334 2/24/2021 12/31/2021 219 2/23/2021 12/31/2021 185 2/25/2021 12/31/2021 150 2/1/2021 12/31/2021 15 2/21/2021 12/31/2021 270 3/23/2021 12/31/2021 102 3/11/2021 12/31/2021 37 3/31/2021 12/31/2021 198 3/28/2021 12/31/2021 45 3/22/2021 12/31/2021 13 3/16/2021 12/31/2021 17 3/24/2021 12/31/2021 159 3/8/2021 12/31/2021 106 3/31/2021 12/30/2021 228 3/9/2021 12/31/2021 245 3/18/2021 12/31/2021 242 3/15/2021 12/31/2021 129 4/6/2021 12/31/2021 118 4/29/2021 12/31/2021 8 4/22/2021 12/31/2021 12 4/30/2021 12/31/2021 30 4/29/2021 12/30/2021 16 4/23/2021 12/31/2021 54 4/12/2021 12/31/2021 63 4/20/2021 12/31/2021 1 4/23/2021 12/30/2021 141 5/5/2021 12/31/2021 186 5/4/2021 12/31/2021 1 5/24/2021 12/30/2021 19 5/20/2021 12/31/2021 226 5/12/2021 12/31/2021 63 5/24/2021 12/31/2021 16 5/28/2021 12/31/2021 20 5/27/2021 12/31/2021 72 5/14/2021 12/31/2021 71 5/25/2021 12/31/2021 5 5/6/2021 12/30/2021 374 5/19/2021 12/31/2021 274 5/2/2021 12/30/2021 24 5/9/2021 12/30/2021 89 5/6/2021 12/31/2021 13 5/11/2021 12/30/2021 60 5/3/2021 12/31/2021 13 5/13/2021 12/30/2021 26 5/10/2021 12/30/2021 4 5/17/2021 12/31/2021 4 5/11/2021 12/31/2021 1 5/12/2021 12/30/2021 15 5/13/2021 12/31/2021 2 5/31/2021 12/30/2021 Expected Output
Month Sum(License Qty) Rolling License Qty 1 149,163 149,163 2 903 150,066 3 1662 151,728 4 431 152,159 5 1719 153,878
- Ashish_Mathur
Hi,
Shares some data (in a format the can be pasted in an MS Excel file), explain the question and show the expected result.
Hi,
We need to calculate running total of license qty between start and end date . Where both the dates are inactive releationship with master calender as shown in my post
Below is the data
Licence Qty Start _date Expired _date 148,361 1/1/2021 12/31/2021 802 1/28/2021 12/31/2021 334 2/24/2021 12/31/2021 219 2/23/2021 12/31/2021 185 2/25/2021 12/31/2021 150 2/1/2021 12/31/2021 15 2/21/2021 12/31/2021 270 3/23/2021 12/31/2021 102 3/11/2021 12/31/2021 37 3/31/2021 12/31/2021 198 3/28/2021 12/31/2021 45 3/22/2021 12/31/2021 13 3/16/2021 12/31/2021 17 3/24/2021 12/31/2021 159 3/8/2021 12/31/2021 106 3/31/2021 12/30/2021 228 3/9/2021 12/31/2021 245 3/18/2021 12/31/2021 242 3/15/2021 12/31/2021 129 4/6/2021 12/31/2021 118 4/29/2021 12/31/2021 8 4/22/2021 12/31/2021 12 4/30/2021 12/31/2021 30 4/29/2021 12/30/2021 16 4/23/2021 12/31/2021 54 4/12/2021 12/31/2021 63 4/20/2021 12/31/2021 1 4/23/2021 12/30/2021 141 5/5/2021 12/31/2021 186 5/4/2021 12/31/2021 1 5/24/2021 12/30/2021 19 5/20/2021 12/31/2021 226 5/12/2021 12/31/2021 63 5/24/2021 12/31/2021 16 5/28/2021 12/31/2021 20 5/27/2021 12/31/2021 72 5/14/2021 12/31/2021 71 5/25/2021 12/31/2021 5 5/6/2021 12/30/2021 374 5/19/2021 12/31/2021 274 5/2/2021 12/30/2021 24 5/9/2021 12/30/2021 89 5/6/2021 12/31/2021 13 5/11/2021 12/30/2021 60 5/3/2021 12/31/2021 13 5/13/2021 12/30/2021 26 5/10/2021 12/30/2021 4 5/17/2021 12/31/2021 4 5/11/2021 12/31/2021 1 5/12/2021 12/30/2021 15 5/13/2021 12/31/2021 2 5/31/2021 12/30/2021 Expected Output
Month Sum(License Qty) Rolling License Qty 1 149,163 149,163 2 903 150,066 3 1662 151,728 4 431 152,159 5 1719 153,878 - Ashish_Mathur
Hi,
The way you have described it, there is no use of the Expiry date column. You may download my PBI file from here.
Hope this helps.
Hi Sir Need to calculate the sum of license qty between the start date and expried date . need to exclude the records if Difference between start and expored date is greater than 365 days .
in my power bi report , i have two table . One table is given below and another table is master calender .
Below is the sample data
start_date expired_date license_quantity 1/1/2016 12/31/2016 15 12/31/2021 12/30/2022 167 1/1/2019 12/31/2019 176 3/23/2020 12/31/2020 30 3/23/2020 12/31/2020 150 1/1/2020 12/31/2020 150 1/1/2018 12/31/2018 -25 1/1/2018 12/31/2018 35 1/1/2016 12/31/2016 86 12/12/2014 12/31/2014 107 2/10/2022 12/31/2022 165 1/1/2021 12/31/2021 121 1/1/2019 12/31/2019 3 1/1/2019 12/31/2019 184 1/1/2021 12/31/2021 121 9/9/2018 12/31/2018 1 9/9/2018 12/31/2018 2 9/9/2018 12/31/2018 1 9/9/2018 12/31/2018 1 9/9/2018 12/31/2018 1 9/9/2018 12/31/2018 1 9/9/2018 12/31/2018 169 1/1/2019 12/31/2019 3 1/1/2019 12/31/2019 184 1/1/2023 12/31/2023 21 5/4/2021 12/31/2021 1 2/18/2021 3/26/2021 1 2/18/2021 3/26/2021 1 1/1/2021 12/31/2021 1 1/1/2021 12/31/2021 18 11/12/2020 12/30/2020 1 5/4/2021 12/31/2021 1 2/18/2021 3/26/2021 1 2/18/2021 3/26/2021 1 1/1/2021 12/31/2021 1 10/25/2021 12/31/2021 20 9/2/2021 12/31/2021 200 1/1/2020 12/31/2020 13 1/1/2019 12/31/2019 5 1/1/2019 12/31/2019 10 1/1/2019 12/31/2019 5 1/1/2017 12/31/2017 246 1/1/2018 12/31/2018 200 2/25/2022 12/31/2022 141 12/31/2021 12/30/2022 79 1/1/2021 12/31/2021 10 1/1/2021 12/31/2021 74 1/1/2020 12/31/2020 1 1/1/2020 12/31/2020 85 2/19/2019 12/31/2019 15 - Ashish_Mathur
Hi,
As requested in my message on January 19, 2023, show the expected result very clearly.
