Count number of days when condition is met

[KristinaSp]

Hi all,

Could anyone helt me with this measure creating?

1) I have to calculate average daily time of cargoes;

2) When check if calculated average daily time is less than target time (daily basis);

3) Then calculate how many days in month the target time was met;

[Anonymous · Accepted solution]

Hi  @KristinaSp ,

Here are the steps you can follow：

1. Create calculated column.

Count = COUNTAX(FILTER( ALL('Main_Table'),'Main_Table'[Date]=EARLIER('Main_Table'[Date])),[Date])

2. Create measure.

Averge daily time(minutes) =
var _1=CALCULATE(SUM('Main_Table'[Time(minutes)]),FILTER(ALL(Main_Table),'Main_Table'[Date]=MAX('Main_Table'[Date])))
return
IF(
    MAX('Main_Table'[Count])=1,_1,
_1 / 2)

Target time ls met measure =
IF(
   [Averge daily time(minutes)] >MAX('Main_Table'[Target time(minutes)]),"No","Yes")

Days number when target time is met =
var _summarize=SUMMARIZE('Main_Table',Main_Table[Date],"1",'Main_Table'[Averge daily time(minutes)],"2",'Main_Table'[Target time ls met measure])
return
COUNTX(FILTER(_summarize,YEAR([Date])=YEAR(MAX([Date]))&&MONTH([Date])=MONTH(MAX([Date]))&&[2]="Yes"),[2])

Total days =
var _summarize=SUMMARIZE('Main_Table',Main_Table[Date],"1",'Main_Table'[Averge daily time(minutes)],"2",'Main_Table'[Target time ls met measure])
return
COUNTX(FILTER(_summarize,YEAR([Date])=YEAR(MAX([Date]))&&MONTH([Date])=MONTH(MAX([Date]))),[2])

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@KristinaSp , Create measures like

Actual = sum(MainTable[Time in Min])
Target = sum(Target[Time in Min])

Met = sumx(values('Date'[Date]), calculate(if([Actual]>[Target],1, 0)))