cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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:

 Date Total Required 24/11/2021 20 20 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
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.

The final output is shown below:

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.

2 REPLIES 2
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.

The final output is shown below:

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.

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())

Announcements

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors