Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Doing something wrong in the statement

The goal of this statement is to take the first Monday (flagged correctly), take the sum of the measure ONLY for that day and the simply divide by 10 so that by the last Friday the measure is zero. It's resetting on the next Monday back to the original, and I'm lost as to why.

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

 

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

 

 

Column = FORMAT([Date],"dddd")
 

 

 

Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , Anonymous 

    I'm sorry,this is my mistable.

    You can use this dax to solve your problem:

    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=IF( DATEDIFF(_first_mon,_date  ,DAY)>=7 , DATEDIFF(_first_mon,_date  ,DAY)-2,DATEDIFF(_first_mon,_date  ,DAY))
var _weekday =WEEKDAY(_date,2)
return
IF(_weekday in {6,7},BLANK(),_first_mon_value - DIVIDE(_first_mon_value,9) * _minus)

    The result is as follows:

     

    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

1 Reply

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    Hi , Anonymous 

    I'm sorry,this is my mistable.

    You can use this dax to solve your problem:

    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=IF( DATEDIFF(_first_mon,_date  ,DAY)>=7 , DATEDIFF(_first_mon,_date  ,DAY)-2,DATEDIFF(_first_mon,_date  ,DAY))
var _weekday =WEEKDAY(_date,2)
return
IF(_weekday in {6,7},BLANK(),_first_mon_value - DIVIDE(_first_mon_value,9) * _minus)

    The result is as follows:

     

    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