cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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!:
Mastering Power BI 2nd Edition

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!:
Mastering Power BI 2nd Edition

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors