topic Re: Identify all items (rows) that does not exists in previous month. in DAX Commands and Tips

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

caio_queiroz — Thu, 04 Nov 2021 22:27:35 GMT

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

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

Jihwan_Kim — Fri, 05 Nov 2021 04:11:43 GMT

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.

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] )

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

v-robertq-msft — Tue, 09 Nov 2021 08:37:19 GMT

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.