Forum Discussion
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.
Hi, Anonymous
You need to create a index column in query editor firstThen 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.
8 Replies
- VijayP
Community Champion
Anonymous
Is it possible to share a sample data . The explanation is not giving the context .
- 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]))RETURNIF(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
Community Support
Hi, Anonymous
You need to create a index column in query editor firstThen 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.
- amitchandak
Super User
Anonymous , 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. 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 - greenOn 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.
