cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Abduvali
Skilled Sharer
Skilled Sharer

Reduce total count by 1 for future dates

Hi All,

 

I hope someone will be able to help with the following. I'm trying to reduce given total by 1 for each day or future dates untill total gets to 0. Please see sample data below:

DateTotalRequired
24/11/20212020
25/11/2021 19
26/11/2021 18
27/11/2021 17
28/11/2021 16
29/11/2021 15
30/11/2021 14
01/12/2021 13
02/12/2021 12
03/12/2021 11

 

Thanks

 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @Abduvali ;

You could try it.

1.create a measure.

total2 = 
var _value=MAXX(ALL('Table'),[Total])- DATEDIFF(MINX(ALL('Table'),[Date]),MAX('Table'[Date]),DAY)
return IF(_value>=0,_value)

2.deselect show items with no data.

vyalanwumsft_0-1638169596965.png

The final output is shown below:

vyalanwumsft_1-1638169672842.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yalanwu-msft
Community Support
Community Support

Hi, @Abduvali ;

You could try it.

1.create a measure.

total2 = 
var _value=MAXX(ALL('Table'),[Total])- DATEDIFF(MINX(ALL('Table'),[Date]),MAX('Table'[Date]),DAY)
return IF(_value>=0,_value)

2.deselect show items with no data.

vyalanwumsft_0-1638169596965.png

The final output is shown below:

vyalanwumsft_1-1638169672842.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Abduvali , Try a measure like

 


measure =
var _min = maxx(allselected(Table), Table[Date])
var _1 =calculate(Sum(Total), all(date) - countx(filter(all(Date), Date[Date] <= max(Date[Date]) && Date[Date] >=_min), date[Date])
return
if(_1>=0, _1, blank())

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors