cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.  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]))&&="Yes"),)``````
``````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]))),)``````

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]))&&="Yes"),)``````
``````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]))),)``````

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 #### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features. #### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator. #### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day! #### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings. Top Solution Authors
Top Kudoed Authors
Users online (4,076)