Forum Discussion
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.
android1,
Please check the dax in modified PBIX file below.
https://1drv.ms/u/s!AhsotbnGu1NolAkqZQW95jsMkso4
Regards,
Lydia
6 Replies
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?
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.
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,
Please check the dax in modified PBIX file below.
https://1drv.ms/u/s!AhsotbnGu1NolAkqZQW95jsMkso4
Regards,
LydiaThat 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
