topic Re: Cumulative Emission with Filter condition in DAX Commands and Tips

Cumulative Emission with Filter condition

sam_hoccane — Fri, 22 Jul 2022 16:23:57 GMT

Hi Community,

Could you please help me finding cummulative emission with Filter condition

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

Re: Cumulative Emission with Filter condition

amitchandak — Fri, 22 Jul 2022 16:53:41 GMT

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

Re: Cumulative Emission with Filter condition

Anonymous — Tue, 26 Jul 2022 04:05:21 GMT

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

If I have misunderstood your meaning, please provide your pbix file without privacy information and your desired output with more details.

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.

Re: Cumulative Emission with Filter condition

sam_hoccane — Thu, 28 Jul 2022 13:01:02 GMT

amitchandak Thank you. It wokred .