Divide a sum into a linear trend to zero based on 10 working days

[Anonymous]

How would I take the first Monday(earliest weekday)  in this table and divide the count of StoryPoints evenly to zero with the last Friday in the series having a zero-sum. In other words, 124/10 = 12.4, so each weekday would have 12.4 subtracted from the previous weekdays number

Mon, 29 Aug 2022	124
Tue, 30 Aug 2022	116.6 (124-12.4)
Wed, 31 Aug 2022	99.2 (116.6 -12.4)

[Anonymous]

Thank you @v-yueyunzh-msft 

I'd like the measure to start with the first Monday and subtract to zero ending 10 working days later like the red line

[Anonymous]

@v-yueyunzh-msft , I was thinking of something like this? But then what would happen to the measure over the weekends? Can we just not do the calc if the IsWeekday flag is False?

[v-yueyunzh-msft]

Hi , @Anonymous 

To your question, i have answered in your new case,you can check if that dax can meet your need ?

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous

Here are the steps you can refer to :

(1)This is my test data :

(2)We can create a calculated column to judge the first Monday we need in your Date table:

Flag = 
var _cur_date = 'Table1'[Date]
var _t  = FILTER( 'Table1','Table1'[Column]= "Monday"  &&  'Table1'[Date] <= _cur_date)
var _rank =  IF( 'Table1'[Column]="Monday"  ,   COUNTROWS(_t) ,BLANK())
return 
 IF( ISODD(_rank),1,BLANK() )

The result is as follows：

(3)We can create a measure :

Measure = var _date =SELECTEDVALUE('Table1'[Date])
var _first_mon  =MAXX( FILTER( ALLSELECTED('Table1') , 'Table1'[Date] <= _date && 'Table1'[Flag] <> BLANK()) ,[Date])
var _first_mon_value = CALCULATE( SUM('Table1'[StortyPoints]) , 'Table1'[Date] =_first_mon)
var _minus=WEEKDAY(_date,2)-1
return
_first_mon_value - DIVIDE(_first_mon_value,10) * _minus

(4)Then we put the filed we need in the viusal , then we can meet your need :

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly