Forum Discussion

sam_hoccane's avatar
sam_hoccane
Icon for Helper I rankHelper I
4 years ago
Solved

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
 
 

 


My expected results should look like this

 

Thankyou in Anticipation

Cumulative Emissions
  • amitchandak's avatar
    amitchandak
    4 years ago

    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's avatar
    Anonymous
    4 years ago

    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.

     

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
    Anonymous
    Not applicable
    4 years ago

    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.