cancel
Showing results for
Did you mean:
Helper I

## Calculate number of days if condition is matched

Hi All,

I have to calculate number of days if condition is matched. Please find the below table:

1. Columns name are : Date , brand, Average coverage

2. Average coverage is a calculated metrics and the used formula as (metrics = AVERAGE('Promoter article'[Coverage]) )

3. Now if I select any brand like "Aardee" then for 1st march 2022 to 31st march 2022 - matched and not matched value will count.

Aardee = Matched = 10, not matched = 17

 Date Brand Avg Coverage condition 3/1/2022 Aardee 83% Matched 3/2/2022 Aardee 42% not matched 3/3/2022 Aardee 92% Matched 3/4/2022 Aardee 79% not matched 3/5/2022 Aardee 71% not matched 3/6/2022 Aardee 71% not matched 3/7/2022 Aardee 92% Matched 3/8/2022 Aardee 58% not matched 3/10/2022 Aardee 92% Matched 3/11/2022 Aardee 79% not matched 3/12/2022 Aardee 79% not matched 3/13/2022 Aardee 29% not matched 3/14/2022 Aardee 58% not matched 3/15/2022 Aardee 58% not matched 3/17/2022 Aardee 58% not matched 3/18/2022 Aardee 0% not matched 3/19/2022 Aardee 57% not matched 3/20/2022 Aardee 50% not matched 3/21/2022 Aardee 92% Matched 3/22/2022 Aardee 92% Matched 3/24/2022 Aardee 92% Matched 3/25/2022 Aardee 79% not matched 3/26/2022 Aardee 57% not matched 3/27/2022 Aardee 71% not matched 3/28/2022 Aardee 92% Matched 3/29/2022 Aardee 83% Matched 3/31/2022 Aardee 92% Matched

Thanks

Shipra

1 ACCEPTED SOLUTION
Community Support

Hi @ShipraJain ,

Please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
VAR _month =
MONTH ( MAX ( 'Table'[Date] ) )
VAR _year =
YEAR ( MAX ( 'Table'[Date] ) )
RETURN
CALCULATE (
COUNT ( 'Table'[condition] ),
FILTER (
ALL ( 'Table' ),
MONTH ( MAX ( 'Table'[Date] ) ) = _month
&& YEAR ( MAX ( 'Table'[Date] ) ) = _year
&& 'Table'[Brand] = SELECTEDVALUE ( 'Table'[Brand] )
&& 'Table'[condition] = SELECTEDVALUE ( 'Table'[condition] )
)
)

Best Regards

Community Support Team _ Polly

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

3 REPLIES 3
Community Support

Hi @ShipraJain ,

Please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
VAR _month =
MONTH ( MAX ( 'Table'[Date] ) )
VAR _year =
YEAR ( MAX ( 'Table'[Date] ) )
RETURN
CALCULATE (
COUNT ( 'Table'[condition] ),
FILTER (
ALL ( 'Table' ),
MONTH ( MAX ( 'Table'[Date] ) ) = _month
&& YEAR ( MAX ( 'Table'[Date] ) ) = _year
&& 'Table'[Brand] = SELECTEDVALUE ( 'Table'[Brand] )
&& 'Table'[condition] = SELECTEDVALUE ( 'Table'[condition] )
)
)

Best Regards

Community Support Team _ Polly

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

Solution Sage

Hi:

Please see attached file for hopeful solution. Good luck..

Condition Not Matched = CALCULATE(COUNTROWS('Table'),'Table'[condition] = "not matched")

Then I used measures to sum the hits under each result.

Cond Matched = SUM('Table'[Condition Matched])
Cond Not Matched = SUM('Table'[Condition Not Matched])
Solution Specialist

Perhaps something like...

[Matched Days] =
CALCULATE(
DISTINCTCOUNT( T[Date] ),
KEEPFILTERS(
T[Condition] = "matched"
)
)

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors