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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
android1
Post Patron
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.

2018-06-26 16_57_22-Workforce WIP - Power BI Desktop.jpg

 

 

1 ACCEPTED SOLUTION
v-yuezhe-msft
Microsoft Employee
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.

View solution in original post

6 REPLIES 6
v-yuezhe-msft
Microsoft Employee
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.

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

Greg_Deckler
Super User
Super User

Tough to say exactly. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler,

 

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.

 

 

2018-06-26 17_53_59-Window.png

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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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