calculate sum between two dates

[Anonymous]

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 . 

@amitchandak @Greg_Deckler 

[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.

[Anonymous]

Hi Sir

for the given below sample data we need calculate rolling sum per year . 

Please help me sir ..

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

Hi Sir,

we should use expiry date to calculate the license qty which fall in between the selected year . 

i have records where date diff between the start and end is more than 365 days .

Below is one example

If a user select year 2021 .. it means we need to calcualte the license qty between 01/01/2021 and 31/12/2021 

License Qty	Start_date	Expired_date	Status
148,361	1/1/2021	12/31/2021	Should Consider
25	11/1/2021	12/30/2022	Should Not consider as expiry date is beyond 31/12/2021

Please help me sir .. 

[Ashish_Mathur]

Hi,

i will not be able to help unless you take a representative example and show the expected result very clearly.

[Anonymous]

Hi 

Thanks for quick response ..

i will try it .. 

[Anonymous]

Need Help @bolfri , @Ashish_Mathur , @Idrissshatila 

its urgent 

[amitchandak]

@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/882970

Power 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])))

[Anonymous]

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

[Anonymous]

i have seen the HR analytics logics and i have implemented it .. but i m not getting correct results ..