cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I

## Count number of days when condition is met

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;

1 ACCEPTED SOLUTION
Community Support

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

2 REPLIES 2
Community Support

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

Super User

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

#### Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors