cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors