Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Mich_J
Frequent Visitor

Cumulative total between two dates in monthly allocation

Hello, I m trying to figure out formula to display monthly total between two dates for one record. Data sample:

CUSTOMERSTART DATEEND DATEMONTHLY GP
9955527/01/202027/04/202010
9977809/01/202027/08/202020
9981601/04/202027/10/202015
711115/04/202027/10/202010
714710/04/202031/12/202015
71501/04/202027/08/202030

 

Result should be

CUSTOMERSTART DATEEND DATEMONTHLY GP2020-012020-022020-032020-042020-052020-062020-072020-082020-092020-102020-112020-12
9955527/01/202027/04/20201010101010        
9977809/01/202027/08/2020202020202020202020    
9981601/04/202027/10/202015   15151515151515  
711115/04/202027/10/202010   10101010101010  
714710/04/202031/12/202015   151515151515151515
71501/04/202027/08/202030   3030303030    

 

Ideally it would be 

CumulativeBetweenDates:=TOTALYTD(SUM(Opportunity[BaseYear_MonthAGP]),
'Calendar'[Day],USERELATIONSHIP([START DATE],'Calendar'[Day]),[END DATE]])

Unfortunatelly last parameter is a string and can't be used in this manner. 

This would be run on SSAS threfore some limitations are in place(Direct Query, VAR not available)

Pointing to right direction would be appriciated

 

Thanks in advance 

Michal

 

1 ACCEPTED SOLUTION

Hello @v-alq-msft  and @amitchandak ,

Thanks for your support on this. Unfortunately both of the solutions did not solve calculation in SSAS environment but taking under consideration both comments and advice from local guru I came to above which did allocate GP across months.   

 

CumulativeBetweenDates:=CALCULATE(
	SUM(Customer[DAY_AGP]),
	FILTER(CALCULATETABLE(Customer,ALL('Calendar')),
		Customer[START_DATE] <=MAX('Calendar'[Day])&&
		Customer[END_DATE]>MIN('Calendar'[Day])
				))

 

View solution in original post

7 REPLIES 7
v-alq-msft
Community Support
Community Support

Hi, @Mich_J 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

c1.png

 

Calendar(a calculated table):

Calendar = CALENDARAUTO()

 

There is no relationship between two tables. You may create three calculated columns and a measure as below.

Calculated column:
Year-Month = VALUE(FORMAT('Calendar'[Date],"yyyymm"))
StartYearMonth = VALUE(FORMAT('Table'[START DATE],"yyyymm"))
EndYearMonth = VALUE(FORMAT('Table'[END DATE],"yyyymm"))

Measure:
Result = 
var _cYearMonth = SELECTEDVALUE('Calendar'[Year-Month])
var _startYearMonth = SELECTEDVALUE('Table'[StartYearMonth])
var _endYearMonth = SELECTEDVALUE('Table'[EndYearMonth])
return 
IF(
    _cYearMonth>=_startYearMonth&&_cYearMonth<=_endYearMonth,
    SUM('Table'[MONTHLY GP])
)

 

Result:

c2.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello @v-alq-msft  and @amitchandak ,

Thanks for your support on this. Unfortunately both of the solutions did not solve calculation in SSAS environment but taking under consideration both comments and advice from local guru I came to above which did allocate GP across months.   

 

CumulativeBetweenDates:=CALCULATE(
	SUM(Customer[DAY_AGP]),
	FILTER(CALCULATETABLE(Customer,ALL('Calendar')),
		Customer[START_DATE] <=MAX('Calendar'[Day])&&
		Customer[END_DATE]>MIN('Calendar'[Day])
				))

 

Hi @Mich_J ,

 

thanks for sharing this. 

It solved my requirement.

amitchandak
Super User
Super User

@Mich_J , I did not get what you want to give the last parameter. You need to tell where year is going to end. In case it end at 12/31 no need for that. It is optional.

like

TOTALYTD(SUM(Opportunity[BaseYear_MonthAGP]),
'Calendar'[Day],USERELATIONSHIP([START DATE],'Calendar'[Day]),"12/31")

 

TOTALYTD(SUM(Opportunity[BaseYear_MonthAGP]),
'Calendar'[Day],USERELATIONSHIP([START DATE],'Calendar'[Day]),"3/31") // Year ending March

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@amitchandak This has to be dynamic parameter and its different for each record to achieve total between two dates therefore above won’t work. It needs to be time intelligence calculation but I cant find any references on web. Most calculations would work to summarise for each month based on date in one column. Where in here I would like to present monthly cumulative between two dates.

@Mich_J , refer if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

Or this file this sum up days between dates

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@amitchandak I will try to evaluate your solution futher but taking my requirement and your example you would like to show 

 

EmployeeStartEnd2020-1 2020-2 2020-3 2020-4 2020-5 2020-6 2020-7
A03/03/2020 03/05/20200011100
B10/02/202004/04/20200111000
          

 

Will your calculations cover this view?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.