Count of days with losses

[Sefino535]

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. 

[Jihwan_Kim · Accepted solution]

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

[Sefino535]

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! 

[Sefino535]

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. 

[FreemanZ]

hi @Sefino535 

try to create a measure with this:

DaysLost =

CALCULATE(

    COUNTROWS(

        FILTER(

            TableName,

            TableName[Loss]<>0

        )

     ),

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

)

[Sefino535]

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

[FreemanZ]

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. 

[Sefino535]

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!