Summing Values BETWEEN Two Dates AND Count consecutive days In Power BI

[Anonymous]

如何寫出出量值，可以計算出篩選的範圍日期，每天連續請求2件，天算超過一次

 

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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

[Anonymous · Accepted solution]

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

[Anonymous]

v-yangliu-msft

I try but wrong , I dont' understand

[Anonymous]

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

[Anonymous]

HI,v-yangliu-msft

Can  mod and over_two be converted into Measures?Because the calculated value will change with the filtered date range

[lbendlin]

Here is a proposed solution as a calculated column.  I am not sure if you are distinguishing between odd and even days?

[Anonymous]

no odd and even numbers, but dates are not double counted,I want like this

THANKS