Show difference between 2 calculated columns filtered by week number

[android1]

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.

[Anonymous · Accepted solution]

@android1,

Please check the dax in modified PBIX file below.

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

Regards,
Lydia

[android1]

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]

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?

[android1]

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.

[Greg_Deckler]

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.

[android1]

Hi @Greg_Deckler,

Here's a sample of my file -> https://www.dropbox.com/s/jwiomu1ss6xikc0/Workforce%20WIP%202.pbix?dl=0