Forum Discussion

Abduvali's avatar
Abduvali
Skilled Sharer
4 years ago
Solved

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

 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

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

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    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.