topic Re: Count of days with losses in DAX Commands and Tips

Count of days with losses

Sefino535 — Fri, 09 Dec 2022 21:43:00 GMT

Hello guys,

I hope you can help me to write a formula that tells me how many days I have lost on the individual products in the past 30 days. The formula should identify today's day -30 so it will be automatically rolling that past.

Data contain a day calendar and the days without losses are 0 (zero) and lost is a number per day.

ID_ Prod	Loss in the past 30 days
Prod 1	10
Prod 2	3
Prod 3	23
Prod 4	0
Prod 5	15

If you got any further questions feel free to ask.

Re: Count of days with losses

FreemanZ — Fri, 09 Dec 2022 23:51:02 GMT

hi Sefino535

try to create a measure with this:

DaysLost =

CALCULATE(

COUNTROWS(

FILTER(

TableName,

TableName[Loss]<>0

)

DATESINPERIOD(Calendar[Date], TODAY(), -30, DAY)

)

Re: Count of days with losses

Sefino535 — Sat, 10 Dec 2022 12:19:14 GMT

Hi FreemanZ,

I have an issue, with a calculation, I have more "days lost" than 30 which should be the maximum. Could it be that there is also a customer-level loss? Can we narrow it down to loss on Material ID?

See the below result...

Re: Count of days with losses

FreemanZ — Sat, 10 Dec 2022 13:58:27 GMT

the issue of "more "days lost" than 30" depends on your data model, it is possible when there were multiple records/rows per day.

Can we narrow it down to loss on Material ID? That also depends on your model. The measure itself has nothing to do with Material ID.

A piece of advice:

when testing new measures, try with simple setup first, like a table visual with one field plus the measure only.

Re: Count of days with losses

Sefino535 — Sat, 10 Dec 2022 18:16:49 GMT

Hi,

...well is possible that model has more rows per day as a loss. Any Idea how to overcome this this so it will count all losses in a day as "one day" ?
Anyone help appreciate!

Re: Count of days with losses

Sefino535 — Sun, 11 Dec 2022 20:42:01 GMT

Hi guys,

Any suggestion for this problem? I looked up anywhere and I can't get It to work it! Still get more losses than up 30 days, caused by different types of loss = multiple rows counts.

I wish to count any loss of a day as one day lost out of the past 30 days.

I hope it make sense.

Re: Count of days with losses

Jihwan_Kim — Mon, 12 Dec 2022 08:03:53 GMT

Hi,

Please try writing a measure something like below.

DayLost Measure: = CALCULATE ( COUNTROWS ( FILTER ( DISTINCT ( 'Calendar'[Date] ), [Losses] <> 0 ) ), DATESINPERIOD ( 'Calendar'[Date], TODAY (), -30, DAY ) )

Re: Count of days with losses

Sefino535 — Mon, 12 Dec 2022 10:51:10 GMT

Thanks Jihwan_Kim,

Your solution works perfectly as expected! The formula does not count single rows, but days of losses.

Many help you are top!