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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
caio_queiroz
Frequent Visitor

Identify all items (rows) that does not exists in previous month.

Hello All.

 

I have a table Resource Cost, with my resource costs in Septmeber and October, i want to compare the costs between the two month, but i also want to take off what is new (it means resources created in October) to use it for the compare.

 

I have tried several ways, but none i got what i need.

 

Could you guys help me?

btw i have a calendar table in my dashboard and there is a relationship with my resources tables

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Since there is no sample data model, I created it by myself.

I am not sure if it fits your situation.

Check the below picture and the attached pbix file below.

All measures are in the attached pbix file.

 

Picture1.png

 

October new resouces: =
VAR septemberresouces =
CALCULATETABLE ( VALUES ( Data[Resource] ), 'Calendar'[Month] = 9 )
VAR octoberresources =
CALCULATETABLE ( VALUES ( Data[Resource] ), 'Calendar'[Month] = 10 )
VAR octobernewresources =
EXCEPT ( octoberresources, septemberresouces )
RETURN
IF ( COUNTROWS ( octobernewresources ) = 1, "New in October" )
 
Sep & Oct resouces cost difference: =
VAR septemberresouces =
CALCULATETABLE ( Data, 'Calendar'[Month] = 9 )
VAR octoberresources =
CALCULATETABLE ( Data, 'Calendar'[Month] = 10 )
VAR sep_oct_both_resources =
INTERSECT (
SUMMARIZE ( octoberresources, Data[Resource] ),
SUMMARIZE ( septemberresouces, Data[Resource] )
)
VAR cost_diff_addcolumns =
ADDCOLUMNS (
sep_oct_both_resources,
"@costdiff",
CALCULATE (
SUMX ( octoberresources, Data[Cost] ) - SUMX ( septemberresouces, Data[Cost] )
)
)
RETURN
SUMX ( cost_diff_addcolumns, [@costdiff] )
 
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
v-robertq-msft
Community Support
Community Support

Hi, 

Has Jihwan_Kim’s reply helped you to find the solution to this problem?

If so, would you like to mark his reply as a solution so that others can learn from it too?

Thanks in advance!

How to Get Your Question Answered Quickly 

 

Best Regards,

Community Support Team _Robert Qin

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

Jihwan_Kim
Super User
Super User

Hi,

Since there is no sample data model, I created it by myself.

I am not sure if it fits your situation.

Check the below picture and the attached pbix file below.

All measures are in the attached pbix file.

 

Picture1.png

 

October new resouces: =
VAR septemberresouces =
CALCULATETABLE ( VALUES ( Data[Resource] ), 'Calendar'[Month] = 9 )
VAR octoberresources =
CALCULATETABLE ( VALUES ( Data[Resource] ), 'Calendar'[Month] = 10 )
VAR octobernewresources =
EXCEPT ( octoberresources, septemberresouces )
RETURN
IF ( COUNTROWS ( octobernewresources ) = 1, "New in October" )
 
Sep & Oct resouces cost difference: =
VAR septemberresouces =
CALCULATETABLE ( Data, 'Calendar'[Month] = 9 )
VAR octoberresources =
CALCULATETABLE ( Data, 'Calendar'[Month] = 10 )
VAR sep_oct_both_resources =
INTERSECT (
SUMMARIZE ( octoberresources, Data[Resource] ),
SUMMARIZE ( septemberresouces, Data[Resource] )
)
VAR cost_diff_addcolumns =
ADDCOLUMNS (
sep_oct_both_resources,
"@costdiff",
CALCULATE (
SUMX ( octoberresources, Data[Cost] ) - SUMX ( septemberresouces, Data[Cost] )
)
)
RETURN
SUMX ( cost_diff_addcolumns, [@costdiff] )
 
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.