Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
Hello, I m trying to figure out formula to display monthly total between two dates for one record. Data sample:
CUSTOMER | START DATE | END DATE | MONTHLY GP |
99555 | 27/01/2020 | 27/04/2020 | 10 |
99778 | 09/01/2020 | 27/08/2020 | 20 |
99816 | 01/04/2020 | 27/10/2020 | 15 |
7111 | 15/04/2020 | 27/10/2020 | 10 |
7147 | 10/04/2020 | 31/12/2020 | 15 |
715 | 01/04/2020 | 27/08/2020 | 30 |
Result should be
CUSTOMER | START DATE | END DATE | MONTHLY GP | 2020-01 | 2020-02 | 2020-03 | 2020-04 | 2020-05 | 2020-06 | 2020-07 | 2020-08 | 2020-09 | 2020-10 | 2020-11 | 2020-12 |
99555 | 27/01/2020 | 27/04/2020 | 10 | 10 | 10 | 10 | 10 | ||||||||
99778 | 09/01/2020 | 27/08/2020 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | ||||
99816 | 01/04/2020 | 27/10/2020 | 15 | 15 | 15 | 15 | 15 | 15 | 15 | 15 | |||||
7111 | 15/04/2020 | 27/10/2020 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | |||||
7147 | 10/04/2020 | 31/12/2020 | 15 | 15 | 15 | 15 | 15 | 15 | 15 | 15 | 15 | 15 | |||
715 | 01/04/2020 | 27/08/2020 | 30 | 30 | 30 | 30 | 30 | 30 |
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
Solved! Go to 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])
))
Hi, @Mich_J
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
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:
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])
))
@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
@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
Or this file this sum up days between dates
https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0
@amitchandak I will try to evaluate your solution futher but taking my requirement and your example you would like to show
Employee | Start | End | 2020-1 | 2020-2 | 2020-3 | 2020-4 | 2020-5 | 2020-6 | 2020-7 |
A | 03/03/2020 | 03/05/2020 | 0 | 0 | 1 | 1 | 1 | 0 | 0 |
B | 10/02/2020 | 04/04/2020 | 0 | 1 | 1 | 1 | 0 | 0 | 0 |
Will your calculations cover this view?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
88 | |
86 | |
83 | |
64 | |
49 |
User | Count |
---|---|
127 | |
108 | |
88 | |
70 | |
66 |