Counting days for a given value without considering previous value

[Thcferreira]

Hi,
I will try to simplify at most to undermine understanding.

I have a range of dates, for example from January 1st to March 1st.
I have a table with a DATA column and another X column with a value, for example: on March 01 and 02 the value of column X is 1, on 03 and 04 the value of column x is 5, on 05 and 06 the value returns to 1.

I need to make a count of how many days the value of X occurred, but with each new count I should not consider the previous one, example in my filter I selected day 2 (on that day the value of X is 1), my result will be 2 days, because I have the day 01 and 02 with the value 1, when I select the day 04 the value of X will be 5 then my result will be 2 because I have the value 5 on the day 03 and 04, until now all right, the problem is when I select the day 06 (on that day the value of X is 1 again) then the value that is being returned is 4 because it also considers the days 01 and 02, the need is not to count more days 01 and 02 and counted only 05 and 06, because between day 02 and 05 I had different values ​​from 1 to X.

If anyone has any ideas.

[Thcferreira]

Sorry, I'm new to powerbi

I tried to simplify the example for a better understanding, now I'm providing more information, I couldn't attach the pbix.
this need is for a map that measures the transmissibility of each state during the covid pandemic.
so the need to know how many days each state stayed in a certain transmissibility range (this range is defined by the transmissibility) and keep track of the changes in ranges, so I cannot accumulate the values, every time I change the range to present a new value.

Here is an example of the table I am using: State, transmissibility (Rt) and the date of a table called covid

The rules of the range are as follows:
transmissibilidade > 1,5 - red
transmissibilidade >=1 e <=1,5 – orange
transmissibilidade <1 - green

On 05/30/2020 when the range is above 1.5 I have 54 days that I stayed in that range

On 05/31/2020 my range is no longer above 1.5 but <= 1.5 so it changed color

On 06/14/2020 my range returned to 1.5 and then he should start counting the days from scratch again, but he is also counting the days of that first period (05/30/2020 from the first image).

So what's the need? Let him count the days for each track, but when there is a change of track, for example I stayed 2 days in the range> 1.5 then I moved to the range <= 1.5 and then I went back to the range> 1.5 I must count those 2 days that I had stayed in this range in the count.

[amitchandak]

@Thcferreira , it seems more like cumulative

example

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(Sales[Sales Date])))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[VijayP]

@Thcferreira 

Is it possible to share a sample data . The explanation is not giving the context .

[Thcferreira]

Hello, I put more information below.
the measure I am using is accumulating the days, is as follows:

VAR MX_DT = CALCULATE(DISTINCT(COVID[TRANSMISSIBILIDADE]),LASTDATE(COVID[DATA]))

VAR DT = LASTDATE(COVID[DATA])

VAR DT_1 = CALCULATE(DISTINCT(COVID[DATA]),COVID[TRANSMISSIBILIDADE] <1.0,ALL(COVID[DATA]))

--VAR QTD_D = CALCULATE(DISTINCTCOUNT(COVID[DATA]),COVID[TRANSMISSIBILIDADE] = MX_DT,ALL(COVID[DATA]))

RETURN

IF(MX_DT < 1.0,

CALCULATE(DISTINCTCOUNT(COVID[DATA]), COVID[TRANSMISSIBILIDADE] < 1,COVID[DATA] <=DT)

,

IF(AND(MX_DT >=1.0, MX_DT < 1.5),

CALCULATE(DISTINCTCOUNT(COVID[DATA]), AND(COVID[TRANSMISSIBILIDADE] >=1.0,COVID[TRANSMISSIBILIDADE] <1.5),COVID[DATA] <=DT) ,

IF(MX_DT >= 1.5,

CALCULATE(DISTINCTCOUNT(COVID[DATA]), COVID[TRANSMISSIBILIDADE] >= 1.5,COVID[DATA] <=DT)

,0)

)

)

[v-easonf-msft · Accepted solution]

Hi, @Thcferreira 
You need to create a index column in query editor first

Then you can try  to create calculated columns as below:

color flag = 
VAR value1 =
    'COVID'[TRANSMISSIBILIDADE]
RETURN
    SWITCH (
        TRUE (),
        value1 > 1.5, "red",
        value1 >= 1
            && value1 <= 1.5, "orange",
        value1 < 1, "green"
    )

ChangedStatue = 
VAR pre =
    CALCULATE (
        VALUES ( COVID[color flag] ),
        FILTER ( ALL ( COVID ), 'COVID'[Index] = EARLIER ( COVID[Index] ) - 1 )
    )
RETURN
    IF ( ISBLANK ( pre ) || pre = 'COVID'[color flag],"unchanged","changed" )

Counting days = 
VAR _max =
    CALCULATE (
        MAX ( 'COVID'[Index] ),
        FILTER (
            'COVID',
            'COVID'[Index] <= EARLIER ( 'COVID'[Index] )
                && 'COVID'[ChangedStatue] = "changed"
        )
    )
RETURN
    IF ( ISBLANK ( _max ), 'COVID'[Index], 'COVID'[Index] - _max + 1 )

The result will show as below:

For more details,please check the attached file.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Thcferreira]

Thanks, now I'm having trouble calculating the days.

DIAS_NA_FAIXA =

VAR _max =

CALCULATE (

MAX ( 'COVID'[Index]),

FILTER ('COVID','COVID'[Index] < ? EARLIER(COVID[Index]) && 'COVID'[MUDA_FAIXA] ? "SIM")

)

RETURN

IF ( ISBLANK ( _max ), 'COVID'[Index], 'COVID'[Index] - _max + 1 )

[v-easonf-msft]

Hi, @Thcferreira 

The formula  I provided is a calculated column ,rather than a measure...

You can check it in my attached pbix file.

Best Regards,
Community Support Team _ Eason

[Thcferreira]

Hello, then I realized my mistake.
I managed to solve using a measure, it was as follows:

DIAS_TRANSM =

VAR MX_DT = CALCULATE(DISTINCT(COVID[TRANSMISSIBILIDADE]),LASTDATE(COVID[DATA]))

VAR COR = DISTINCT(COVID[VL_FAIXA_TRANSMISSIBILIDADE])

VAR DT = LASTDATE(COVID[DATA])

VAR MUDA_FAIXA_ANT = CALCULATE(DISTINCT(COVID[MUDA_FAIXA]),PREVIOUSDAY(LASTDATE(COVID[DATA])))

VAR ULT_DT_VERDE = CALCULATE(LASTDATE(COVID[DATA]),COVID[VL_FAIXA_TRANSMISSIBILIDADE] = "VERDE",ALL(COVID[DATA]),COVID[DATA]<=DT)

VAR ULT_DT_VERMEL = CALCULATE(LASTDATE(COVID[DATA]),COVID[VL_FAIXA_TRANSMISSIBILIDADE] = "VERMELHO",ALL(COVID[DATA]),COVID[DATA] <= DT)

VAR ULT_DT_LAR = CALCULATE(LASTDATE(COVID[DATA]),COVID[VL_FAIXA_TRANSMISSIBILIDADE] = "LARANJA",ALL(COVID[DATA]),COVID[DATA] <= DT)

VAR FAIXA_ANT = CALCULATE(DISTINCT(COVID[VL_FAIXA_TRANSMISSIBILIDADE]),PREVIOUSDAY(LASTDATE(COVID[DATA])))

VAR FAIXA_ATUAL = VALUES(COVID[VL_FAIXA_TRANSMISSIBILIDADE])

RETURN

IF(COR = "VERDE",

IF(FAIXA_ATUAL <> FAIXA_ANT,1

,CALCULATE(DISTINCTCOUNT(COVID[DATA]), COVID[VL_FAIXA_TRANSMISSIBILIDADE] = "VERDE",COVID[DATA] > ULT_DT_LAR || COVID[DATA] > ULT_DT_VERMEL,COVID[DATA] <=DT)

)

,

IF(COLOR = "ORANGE",

IF(FAIXA_ATUAL <> FAIXA_ANT,

1

,CALCULATE(DISTINCTCOUNT(COVID[DATA]),COVID[VL_FAIXA_TRANSMISSIBILIDADE] = "LARANJA",COVID[DATA]>ULT_DT_VERDE,COVID[DATA]>ULT_DT_VERMEL, COVID[DATA] <= DT)

)

,

IF(COLOR = "RED",

IF(FAIXA_ATUAL <> FAIXA_ANT,

1,

CALCULATE(DISTINCTCOUNT(COVID[DATA]), COVID[VL_FAIXA_TRANSMISSIBILIDADE] = "VERMELHO",COVID[DATA]>ULT_DT_VERDE || COVID[DATA] > ULT_DT_LAR,COVID[DATA] <=DT)

)

,0)

)

)

I thank you all for your help.