Count days with decrease number

[Anonymous]

Hi all,

I have a table that is updated daily with confirmed cases of covid. I need to display the number of days that this number is declining, but I don´t know how to calculate this information.

I set up a simplified table to exemplify:

Date	new cases	Days in decrease
01/08/2020	20	0
02/08/2020	15	1
03/08/2020	12	2
04/08/2020	11	3
05/08/2020	30	0
06/08/2020	50	0
07/08/2020	70	0
08/08/2020	45	1
09/08/2020	30	2
10/08/2020	17	3
11/08/2020	16	4
12/08/2020	18	0

Thanks!

[amitchandak]

@Anonymous ,

new column like new cases = sumx(filter(table, [date] =earlier([date])-1),[new cases])

You can this day vs last day like this with date table

This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('order'[Qty]), previousday('Date'[Date]))

Last Day Non Continuous = CALCULATE([sales],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))
Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))
Diff COlumn = datediff(maxx(filter(Table,Table[Date]<earlier(Table[Date]) && Table[Numberf]= earlier(Table[Numberf]) ),Table[Date]) ,Table[Date],Day)

Power BI — Day Intelligence
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

Hi amitchandak !

I'm so sorry, but I'm just starting with power bi.

In my model I already have a calendar table, I just did the relationship with report date with the calendar date.

After that, I should create measures with this part?

This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('order'[Qty]), previousday('Date'[Date]))

Last Day Non Continuous = CALCULATE([sales],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))
Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))
Diff COlumn = datediff(maxx(filter(Table,Table[Date]<earlier(Table[Date]) && Table[Numberf]= earlier(Table[Numberf]) ),Table[Date]) ,Table[Date],Day)

Thank you!

[camargos88 · Accepted solution]

Hi @Anonymous ,

Try this measure:

_Count_Decrease = 
VAR _tb = SUMMARIZE(ALL('Table'), 'Table'[Date], 'Table'[new cases], "Decrease",
                        VAR _previousDate = CALCULATE(MAX('Table'[Date]), FILTER(ALL('Table'), 'Table'[Date] < EARLIER('Table'[Date])))
                        RETURN
                            IF('Table'[new cases] < CALCULATE(SUM('Table'[new cases]), FILTER(ALL('Table'), 'Table'[Date] = _previousDate)), 1, 0)
)
VAR _currentDate = SELECTEDVALUE('Table'[Date])
VAR _firstZero = CALCULATE(MAX('Table'[Date]), FILTER(_tb, 'Table'[Date] <= _currentDate && [Decrease] = 0)) 
RETURN 
    SUMX(FILTER(_tb, 'Table'[Date] >= _firstZero && 'Table'[Date] <= _currentDate), [Decrease])

[Pragati11]

HI @Anonymous ,

On what basis you want to count days in declining manner?

I am not able to understand the issue clearly. Can you add more details please?

Thanks,

Pragati

[Anonymous]

Hi Pragati11 !

The count should consider the number of consecutive days in which the number of cases (compared to the previous day) shows a decline.

Example:
08/26: 40 cases
08/27: 30 cases
08/28: 20 cases

Today I should display 3 days.

If the table was like:
08/26: 18 cases
08/27: 30 cases
08/28: 20 cases

Today I should display 1 day

If the number on 08/28 was a increase, I should display 0 ...