Calculate diff between rows filtering IDs

Anonymous · ‎10-27-2022

Hi All,

I have a table collecting value each 10 minuts for aprox. 100 different IDs/Tags.

Example of table:

EnergyTagNo	VALUE	TIMESTAMP	Consumption
65	1280934	27-10-2022 11:37
66	4183413	27-10-2022 11:37
64	2460433	27-10-2022 11:37
63	5456617	27-10-2022 11:37
66	4183379	27-10-2022 11:27
65	1280886	27-10-2022 11:27
64	2460318	27-10-2022 11:27
63	5456515	27-10-2022 11:27

I need a solution that calculates the difference between two timestamps (Based on my date table), and filter on each tag.

I tried following solution: https://community.powerbi.com/t5/Desktop/Subtract-value-from-earlier-rows-depending-on-Category/m-p/...

This solution worked as long as I filter out data older than 3 months and narrow down the tags to 4-5. Calculation consumption for a whole year, the report reports and error.

Consumption = 
Fact_EnergyData[_VALUE]
    - MAXX(
        TOPN(
            1,
            FILTER(
                Fact_EnergyData,
                Fact_EnergyData[EnergyTagNo] = EARLIER( Fact_EnergyData[EnergyTagNo] )
                    && Fact_EnergyData[_TIMESTAMP] < EARLIER(Fact_EnergyData[_TIMESTAMP] )
            ),
            Fact_EnergyData[_TIMESTAMP]
        ),
        Fact_EnergyData[_VALUE]
    )

Error:

So, my question. Is there any other options to do the calculation? DAX? Power Query?

I look forward to hear from you!

Thanks a million in advance.

v-yadongf-msft · ‎10-27-2022

Hi @Anonymous ,

How many records do you get data to Power bi side? How many columns your tables have?

If your tables have too many columns, please try to remove unused columns to increase loading and calculation performances and reduce memory cost.

In addition, any nested iterator calculation in your calculated column or measure?

They can cause performance issues and spend more memory resources. (e.g. nested three iterators of 1k row tables will multiply the calculation amount to 1k*1k*1k=100m)

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous · ‎10-28-2022

Hi @v-yadongf-msft

Thank you for your reply - much appreciated. I will try to answer best possible below:

How many records do you get data to Power bi side? How many columns your tables have?

Currently database consist of 8 columns which one is a duplicated column in extract the year, and one is concatenating two columns to create a unique ID. Database has 2 million rows currently and increasing.

If your tables have too many columns, please try to remove unused columns to increase loading and calculation performances and reduce memory cost.

I will try and change how often we log data in the source. Currently it is every 10 minutes. I will change this to every hour. This will in the short run solve the issue. But in the long run I might sooner or later receive the same error.