cancel
Showing results 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

Post Patron

## Show difference between 2 calculated columns filtered by week number

Hi,

I have 2 calculated columns A & B.

Column A returns the total number of workers whose [StartDate] is in 2018 -> Made Active in 2018C = CALCULATE(DISTINCTCOUNT(CarersList[CarerRef]),DATESBETWEEN(CarersList[StartDate],DATE(2018,1,1),DATE(2018,12,31)))

Column B returns the total number of workers whose [DateLeft] is in 2018 -> Made Inactive in 2018C = CALCULATE(DISTINCTCOUNT(CarersList[CarerRef]),DATESBETWEEN(CarersList[DateLeft],DATE(2018,1,1),DATE(2018,12,31)))

I want to show in a matrix table each week number of the year 2018 & the difference between the above 2 columns.

Any idea how this can be achieved? Want something like in below pic.

1 ACCEPTED SOLUTION
Microsoft Employee

@android1,

Please check the dax in modified PBIX file below.

https://1drv.ms/u/s!AhsotbnGu1NolAkqZQW95jsMkso4

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Microsoft Employee

@android1,

Please check the dax in modified PBIX file below.

https://1drv.ms/u/s!AhsotbnGu1NolAkqZQW95jsMkso4

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Post Patron

That works great. Thank you.

USERELATIONSHIP needed in column B. Used with a Date Table.

Column B= CALCULATE(DISTINCTCOUNT(CarersList[CarerRef]),DATESBETWEEN(CarersList[DateLeft],DATE(2018,1,1),date(2018,12,31)),USERELATIONSHIP(MyDateTable[FullDate],CarersList[DateLeft]))+0

Super User

But, perhaps a measure that simply does a SUM of each column and subtracts them?

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Post Patron

I do have the above 2 columns as measures also.

I also have this calc column & measure to get the difference between the 2. Surplus/Deficit 2018 = SUM(CarersList[Made Active in 2018C]) - SUM([Made Inactive in 2018C])

The problem is how do I show this for each week of the year? The 2 columns A & B use different date fields.

Super User

Need to get a sense of what your raw data looks like. Extremely difficult to provide a good answer otherwise as I can only guess at how your data is organized.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Post Patron

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.