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
10 Replies
Here is a proposed solution as a calculated column. I am not sure if you are distinguishing between odd and even days?
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
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 ,
I recommend that you can upgrade Power BI Desktop to the latest version.
Because in the process of version upgrade, the previous functions will be continuously optimized and more comprehensive new functions will appear, and many contents are compatible with the latest version.
What’s new in the lastest Power BI update?
Latest version of Power BI Desktop
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, v-yangliu-msft
I want to upgrade, too,but the company does not give.
by theway if I want to over_two change over_three how to change ?
I try it but it doesn't work
thank you
Hi Anonymous ,
According to the rule: the [Count] of two consecutive dates is the same, and the latter date is displayed as 1.
The count of 2022.3.6 is 3, and the count of 2022.3.7 is 4. The counts of two consecutive dates are inconsistent, and both appear 0.
What is the result you want to get, can you express it with pictures?
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.
According to the rule: continuous two days and both over 3 count calculate 1 and Do not double count dates
thank you
1.I want to filter 3/7~3/29 && Defect=BBBB calculate like it
2.I want to filter 3/9~3/29 && Defect=BBBB calculate like it
3.If I want to change calculate count is over_4 how to change measure?
I want to filter 3/12~3/29 && Defect=AAAAA calculate like it
