Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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) |
Solved! Go to Solution.
Hi , @singleton2787
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
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
@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?
Hi , @singleton2787
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
Hi , @singleton2787
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |