Calculate SUMX & VALUES & Filter EARLIER

[Riddler]

Hi I have a table Projectshistorical with severall ProjectIds in it.

projectdetails.projectId	repositories.repo.loc	repositories.repo.scantimestamp	RollProjectLoc
3	69258	12.04.2019 09:18	69258
3	22380	12.04.2019 09:19	69258+22380
3	69258	12.04.2019 09:37	69258+22380
3	69258	12.04.2019 09:50	69258+22380
3	22380	12.04.2019 09:54	69258+22380
3	136023	12.04.2019 09:55	69258+22380+136023
3	22380	12.04.2019 10:06	69258+22380+136023
3	136023	12.04.2019 10:10	69258+22380+136023

RollProjectLoc = CALCULATE(

SUMX(

VALUES('Projectshistorical'[projectdetails.projectId]);[repositories.repo.loc]);

FILTER('Projectshistorical';'Projectshistorical'[repositories.repo.scantimestamp]<=EARLIER(Projectshistorical[repositories.repo.scantimestamp])))

And it always gives me the answer that it cannot find the repositories.repo.loc if I create a quick measure it tells me that this is a ring dependency...  How do I make this work?

[marab]

Hi all, 

I have a problem with the formula below that should be correct but it doesn't recongnize the column name in the EARLIER function. 

I simply need to sum up the Item Value per each project name like:

Project Name           Item            Item Value                Project Value

A                                 1                       50                               89

A                                 2                       39                               89

B                                 1                       10                               100

B                                 2                       50                               100

B                                 3                       40                               100

Project Value = Sumx(FILTER('Tab_Project','Tab_Project'[Project Name]=EARLIER('Tab_Project'[Project Name],'Tab_Project'[Item Value]))

Any clue? 

a million thanks

[Ashish_Mathur]

Hi,

If you want a calculated column formula, then try this

=calculate(sum('Tab_Project'[Item Value]),FILTER('Tab_Project','Tab_Project'[Project Name]=EARLIER('Tab_Project'[Project Name])))

If you want a measure, then try this

=calculate(sum('Tab_Project'[Item Value]),allexcept('Tab_Project','Tab_Project',['Tab_Project'[Item]]))

Hope this helps.

[v-frfei-msft · Accepted solution]

Hi @Riddler ,

Please check the sample pbix as attached. If it doesn't meet your requirement, kindly share your excepted result to me.

Measure = 
VAR cal =
    CALCULATETABLE (
        DISTINCT ( 'Projectshistorical'[repositories.repo.loc] ),
        FILTER (
            ALLSELECTED ( Projectshistorical ),
            'Projectshistorical'[repositories.repo.scantimestamp]
                <= MAX ( 'Projectshistorical'[repositories.repo.scantimestamp] )
        ),
        VALUES ( Projectshistorical[repositoryId] )
    )
RETURN
    SUMX ( cal, 'Projectshistorical'[repositories.repo.loc] )

[v-frfei-msft]

Hi @Riddler ,

To create a measure as below.

Measure = 
VAR cal =
    CALCULATETABLE (
        DISTINCT ( 'Table1'[repositories.repo.loc] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            'Table1'[repositories.repo.scantimestamp]
                <= MAX ( 'Table1'[repositories.repo.scantimestamp] )
        )
    )
RETURN
    SUMX ( cal, 'Table1'[repositories.repo.loc] )

[Riddler]

Hi @v-frfei-msft 

Wow this looks great and very promising, but it seems not to take into account that I also need to filter on the project ID but instead filters on distinct values of repo.loc. What can happen is that two repos are used in different projects or that two repos in the same project have the same repo.loc. In my table I have also different projects, what I wrote in the text, what was probably a bit misleading. Should have made more examples in the table. My apologies.

So I tried to make it Distinct by project ID but then it actually multiplies repo.loc with the project.id. Do you know how I can make it work?

RollingLOC = 

VAR cal =
    CALCULATETABLE (
        DISTINCT ( 'Projectshistorical'[projectdetails.projectId]);
        FILTER (
            ALLSELECTED ( Projectshistorical );
            'Projectshistorical'[repositories.repo.scantimestamp]
                <= MAX ( 'Projectshistorical'[repositories.repo.scantimestamp] )
        )
    )
RETURN
    SUMX ( cal; 'Projectshistorical'[repositories.repo.loc])

Thanks a lot in advance. This is completely different from how I tried to do that. 

[v-frfei-msft]

Hi @Riddler ,

Update the formula as below.

Measure =
VAR cal =
    CALCULATETABLE (
        DISTINCT ( 'Table1'[repositories.repo.loc] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            'Table1'[repositories.repo.scantimestamp]
                <= MAX ( 'Table1'[repositories.repo.scantimestamp] )
        ),
        VALUES ( 'Projectshistorical'[projectdetails.projectId] )
    )
RETURN
    SUMX ( cal, 'Table1'[repositories.repo.loc] )

[Riddler]

Hi  @v-frfei-msft  thanks for this fast reply.

I tried it.

RollingProjectRepoLoc = 
VAR cal =
    CALCULATETABLE (
        DISTINCT ( 'Projectshistorical'[repositories.repo.loc] );
        FILTER (
            ALLSELECTED ( 'Projectshistorical' );
            'Projectshistorical'[repositories.repo.scantimestamp]
                <= MAX ( 'Projectshistorical'[repositories.repo.scantimestamp] )
        );
        VALUES ( 'Projectshistorical'[projectdetails.projectId] )
    )
RETURN
    SUMX ( cal; 'Projectshistorical'[repositories.repo.loc] )

Which gave me back this table (results are far right)

projectdetails.projectId	repositoryId	repositories.repo.repositoryName	repositories.repo.loc	repositories.repo.scantimestamp	RollingProjectRepoLoc
4	11	angular TS	86183	18.04.2019 12:39	664378
3	8	hbase	136023	12.04.2019 09:55	664378
3	8	hbase	136023	12.04.2019 10:10	664378
3	8	hbase	136023	16.04.2019 15:02	664378
3	8	hbase	136023	16.04.2019 15:16	664378
3	8	hbase	136023	29.04.2019 12:35	664378
4	8	hbase	136023	12.04.2019 09:55	664378
4	8	hbase	136023	12.04.2019 10:10	664378
4	8	hbase	136023	16.04.2019 15:02	664378
4	8	hbase	136023	16.04.2019 15:16	664378
4	8	hbase	136023	29.04.2019 12:35	664378
1	1	Kafka	136970	09.04.2019 20:33	664378
1	1	Kafka	136970	09.04.2019 20:52	664378
4	1	Kafka	136970	09.04.2019 20:33	664378
4	1	Kafka	136970	09.04.2019 20:52	664378
3	5	hivemall(incubating)	69258	12.04.2019 09:18	664378
3	5	hivemall(incubating)	69258	12.04.2019 09:37	664378
3	5	hivemall(incubating)	69258	12.04.2019 09:43	664378
3	5	hivemall(incubating)	69258	12.04.2019 09:50	664378
1	1	Kafka	136970	18.04.2019 17:54	664378
4	1	Kafka	136970	18.04.2019 17:54	664378
1	1	Kafka	136970	29.04.2019 12:29	664378
4	1	Kafka	136970	29.04.2019 12:29	664378
1	1	Kafka	136970	29.04.2019 14:30	664378
4	1	Kafka	136970	29.04.2019 14:30	664378
2	3	lgv	111618	10.04.2019 15:28	664378
2	3	lgv	111618	10.04.2019 17:20	664378
2	3	lgv	111618	29.04.2019 14:27	664378
2	4	ol-cesium	4911	10.04.2019 16:52	664378
3	6	skywalking (incubating)	12361	12.04.2019 09:19	664378
3	6	skywalking (incubating)	12361	12.04.2019 09:39	664378
3	6	skywalking (incubating)	12361	12.04.2019 09:45	664378
3	6	skywalking (incubating)	12361	12.04.2019 09:54	664378
3	6	skywalking (incubating)	12361	12.04.2019 10:06	664378
3	6	skywalking (incubating)	12361	16.04.2019 15:13	664378
3	6	skywalking (incubating)	12361	16.04.2019 15:15	664378
3	6	skywalking (incubating)	12361	29.04.2019 12:50	664378
4	9	three	1521	18.04.2019 09:11	664378
4	9	three	1521	18.04.2019 12:39	664378
4	10	Visual Studio	105533	18.04.2019 09:12	664378

So the value what is calculated is the sum of all repo.locs that are distinct. 

I have the feeling that this goes in the right direction.

As you can see in this table there are different projectsIds and different repoIds . Where I want to sum up the last timestamps repo.locs for each project with its distinct repositories and make it a rolling value.  

Thanks for all the help you put into this.

Greets Riddler