cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Thcferreira
Frequent Visitor

Counting days for a given value without considering previous value

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.

1 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:

118.png

 

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.

 

 

View solution in original post

8 REPLIES 8
Thcferreira
Frequent Visitor

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

Thcferreira_0-1602265654253.png


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

t2.png

 

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

t3.png

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).

 
 
 
 

t.png


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
Super User IV
Super User IV

@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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

VijayP
Super User I
Super User I

@Thcferreira 

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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


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)
)
)

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:

118.png

 

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.

 

 

View solution in original post

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 )
t4.png

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

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.