Get Measure value based on condition DAX

[phoisan]

Hi,

I'm facing problem to calculate current measure value + Previous row measure value

Seq, Time, DP are source data.

Airflow is calculated column = IF(DP< 0.1,0,EXP((LN(DP /4.322)/1.2791)))

Fan Efficiency (%, 1 to 100) is "What-If" parameter

Energy is measure = (DP * Airflow * Time)/(10* Fan Efficinecy)

My problem is how I can calculate the EnergyConsumption value  = Energy + Previous Energy Consumption

Seq	Time	DP	Airflow	Energy	EnergyConsumption
1	0	31.78	4.7578	0.0000	0.0000
2	0.3333	28.73	4.3969	0.1403	0.1403	=0+0.1403
3	0.1667	28.85	4.4113	0.0707	0.2111	=0.1403+0.2111
4	0.1667	28.43	4.3610	0.0689	0.2800	=0.2111+0.28
5	0.1667	29.05	4.4352	0.0716	0.3515	=0.28+0.3515
6	0.1667	25.37	3.9895	0.0562	0.4078	=0.3515+0.4078
7	0.1667	31.05	4.6721	0.0806	0.4884	=0.4078+0.4884
8	0.1667	26.9	4.1764	0.0624	0.5508	=0.4884+0.5508
9	0.1667	32.87	4.8849	0.0892	0.6400	=0.5508+0.64
10	0.1667	26.74	4.1570	0.0618	0.7018	=0.64+0.7018

Here is the Pbix file and excel file link

https://www.dropbox.com/s/5ms8azheh6pfsmt/Sample.pbix?dl=0

https://www.dropbox.com/s/iux4hnvxcyivgei/Sample.xlsx?dl=0

[Greg_Deckler]

Right, so if this is a column, it is pretty standard:

Column = SUMX(FILTER('Table',[Seq] <= EARLIER('Table'[Seq])),[Energy Consumption])

[phoisan]

But the Energy is measure not column. How I can calculate the sum of current energy and previous row energy?

Here is the Pbix file and excel file link

https://www.dropbox.com/s/5ms8azheh6pfsmt/Sample.pbix?dl=0

https://www.dropbox.com/s/iux4hnvxcyivgei/Sample.xlsx?dl=0

[Greg_Deckler]

Well, in that case, you create a table variable using SUMMARIZE or GROUPBY that is essentially the same as your visualization. You use ADDCOLUMNS to add your measure as a column. You then use ADDCOLUMNS again to add your cumulative measure as a column. You then can filter down to the correct row in the table.

The technique is essentially laid out here:  https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[phoisan]

I'm not really understand how to do it. I'm new to the Power BI.

I'll try to understand the articles you shared .

Thanks

[Greg_Deckler · Accepted solution]

@phoisan  I really felt more like going to bed rather than downloading another PBIX tonight, but here you go, updated PBIX is attached below the sig.

Energy Cumulative = 
    VAR __Seq = MAX('Test'[Seq])
    VAR __Table = 
        SUMMARIZE(
            FILTER(ALL('Test'),[Seq]<=__Seq),
            [Seq],
            "Energy",[Energy]
        )
RETURN
    SUMX(__Table,[Energy])

[phoisan]

This is brilliant....fixed my problem. thanks

This issue had been stuck me for past three days!