cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Cumulative Emission with Filter condition

Hi Community,

Data PowerBI File

I want to create a measure that can calculate emission up till Forecast value is "N".  I  am trying with following measure but I am still getting values where forecast is "Y".  so, my goal is create  measure that calculate cummulative total up till  Forecast value is "N" and rest is blank.    I guess it is happenig becasue of ALL.

Actual Cumulative Emissions =
VAR _table = FILTER ( ALLSELECTED(data), data[Date] <= MAX( data[Date])&&data[Forecast]="N")
VAR _cumulative_before_forecast = CALCULATE( SUM(data[Emission]) , _table)
Return   _cumulative_before_forecast

My expected results should look like this

Thankyou in Anticipation

2 ACCEPTED SOLUTIONS
Super User

@sam_hoccane , Try one of the two

Actual Cumulative Emissions =
VAR _max = maxx(filter(allselected(data) ,data[Forecast]="N"), data[Date])
VAR _cumulative_before_forecast = CALCULATE( SUM(data[Emission]) , FILTER ( ALLSELECTED(data), data[Date] <= MAX( data[Date]) && data[Date] <=_max && data[Forecast]="N"))
Return _cumulative_before_forecast

Actual Cumulative Emissions =
VAR _max = maxx(filter(allselected(data) ,data[Forecast]="N"), data[Date])
VAR _cumulative_before_forecast = if( max(data[Date]) <=_max ,CALCULATE( SUM(data[Emission]) , FILTER ( ALLSELECTED(data), data[Date] <= MAX( data[Date]) && data[Forecast]="N")), blank())
Return _cumulative_before_forecast

Anonymous
Not applicable

Hi @sam_hoccane ,

I have created a simple sample, please refer to my pbix file to see if it help you.

Create a measure.

``````Measure =
VAR _result =
CALCULATE (
SUM ( 'Table'[Emission] ),
FILTER (
ALL ( 'Table' ),
'Table'[date] <= SELECTEDVALUE ( 'Table'[date] )
&& 'Table'[Forecasst] = "N"
)
)
RETURN
IF ( MAX ( 'Table'[Forecasst] ) = "N", _result, BLANK () )
``````

Or a column.

``````Column =
VAR _result =
CALCULATE (
SUM ( 'Table'[Emission] ),
FILTER (
( 'Table' ),
'Table'[date] <= EARLIER ( 'Table'[date] )
&& 'Table'[Forecasst] = "N"
)
)
RETURN
IF ( 'Table'[Forecasst] = "N", _result, BLANK () )
``````

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
Helper I

@amitchandak Thank you. It wokred .

Anonymous
Not applicable

Hi @sam_hoccane ,

I have created a simple sample, please refer to my pbix file to see if it help you.

Create a measure.

``````Measure =
VAR _result =
CALCULATE (
SUM ( 'Table'[Emission] ),
FILTER (
ALL ( 'Table' ),
'Table'[date] <= SELECTEDVALUE ( 'Table'[date] )
&& 'Table'[Forecasst] = "N"
)
)
RETURN
IF ( MAX ( 'Table'[Forecasst] ) = "N", _result, BLANK () )
``````

Or a column.

``````Column =
VAR _result =
CALCULATE (
SUM ( 'Table'[Emission] ),
FILTER (
( 'Table' ),
'Table'[date] <= EARLIER ( 'Table'[date] )
&& 'Table'[Forecasst] = "N"
)
)
RETURN
IF ( 'Table'[Forecasst] = "N", _result, BLANK () )
``````

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.

Super User

@sam_hoccane , Try one of the two

Actual Cumulative Emissions =
VAR _max = maxx(filter(allselected(data) ,data[Forecast]="N"), data[Date])
VAR _cumulative_before_forecast = CALCULATE( SUM(data[Emission]) , FILTER ( ALLSELECTED(data), data[Date] <= MAX( data[Date]) && data[Date] <=_max && data[Forecast]="N"))
Return _cumulative_before_forecast

Actual Cumulative Emissions =
VAR _max = maxx(filter(allselected(data) ,data[Forecast]="N"), data[Date])
VAR _cumulative_before_forecast = if( max(data[Date]) <=_max ,CALCULATE( SUM(data[Emission]) , FILTER ( ALLSELECTED(data), data[Date] <= MAX( data[Date]) && data[Forecast]="N")), blank())
Return _cumulative_before_forecast

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors