Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sam_hoccane
Helper I
Helper I

Cumulative Emission with Filter condition

Hi Community,

Data PowerBI File

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
 
 
sam_hoccane_2-1658506133143.png

 


My expected results should look like this

sam_hoccane_1-1658505950484.png

 

Thankyou in Anticipation

2 ACCEPTED SOLUTIONS
amitchandak
Super User
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

View solution in original post

v-rongtiep-msft
Community Support
Community Support

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

vpollymsft_1-1658808294428.png

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.

 

 

View solution in original post

3 REPLIES 3
sam_hoccane
Helper I
Helper I

@amitchandak Thank you. It wokred . 

v-rongtiep-msft
Community Support
Community Support

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

vpollymsft_1-1658808294428.png

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.

 

 

amitchandak
Super User
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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors