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
I am trying to create a measure with the below logic Add 10 till it reaches 140 and then repeat the same value till a particular date.
Date | Current Value | Expected Result |
01/01/2023 | 100 | |
02/01/2023 | 110 | |
03/01/2023 | 120 | |
04/01/2023 | 130 | |
05/01/2023 | 140 | |
06/01/2023 | 140 | |
07/01/2023 | 140 | |
08/01/2023 | 140 | |
09/01/2023 | 140 |
HI @Sandhya877,
You can try to use the following measure formula to get the cumulative value until the target:
formual =
VAR currDate =
MAX ( Table[Date] )
VAR _startValue =
CALCULATE ( MIN ( Table[Current] ), ALLSELECTED ( Table ) )
VAR _startDate =
CALCULATE (
MIN ( Table[Date] ),
FILTER ( ALLSELECTED ( Table ), [Current] <> 0 )
)
RETURN
MIN ( _startValue + DATEDIFF ( _startDate, currDate, MONTH ) * 10, 140 )
Regards,
Xiaoxin Sheng
Thank you.
@Sandhya877 What's your date format? mm/dd/yyyy or dd/mm/yyyy?
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 |
---|---|
2 | |
2 | |
1 | |
1 | |
1 |
User | Count |
---|---|
9 | |
6 | |
3 | |
3 | |
2 |