Forum Discussion
Summing Values BETWEEN Two Dates AND Count consecutive days In Power BI
如何寫出出量值，可以計算出篩選的範圍日期，每天連續請求2件，天算超過一次
Hi Anonymous ,
Create measure.
mod_measure = MOD(MAX('Table'[rank]),2)Over_TWO_measure = var _current=SUMX(FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])),[count]) var _next= IF( MAX('Table'[Date])=MINX(ALL('Table'),'Table'[Date]),_current, SUMX(FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])-1),[count])) return IF( _current=_next&&MAX([count])>=2&&[mod_measure]=0,1,0)Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi Anonymous ,
Here are the steps you can follow：
1. Create calculated column.
rank =
RANKX(ALL('Table'),'Table'[Date],,ASC)mod = MOD('Table'[rank],2)Over_TWO =
var _current=SUMX(FILTER(ALL('Table'),'Table'[Date]=EARLIER('Table'[Date])),[count])
var _next=
IF(
'Table'[Date]=MINX(ALL('Table'),'Table'[Date]),_current,
SUMX(FILTER(ALL('Table'),'Table'[Date]=EARLIER('Table'[Date])-1),[count]))
return
IF(
_current=_next&&[count]>=2&&'Table'[mod]=0,1,0)Because =
var _pre=CALCULATE(SUM('Table'[Over_TWO]),FILTER(ALL('Table'),'Table'[rank]=EARLIER([rank])+1))
var _cu=CALCULATE(SUM('Table'[Over_TWO]),FILTER(ALL('Table'),'Table'[rank]=EARLIER([rank])))
return
IF(
_cu=1||_pre=1,1,0)
2. Place in the matrix and click on Row headers - turn off Stepped layout.
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Can mod and over_two be converted into Measures?Because the calculated value will change with the filtered date range