Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
Anonymous
Not applicable

date

‎08-06-2022 11:11 PM

could anyone help, I have two date fields, 1) created date, 2) completed date on same table. However in column chart I have to show the values for both. But If I keep Created date as X axis the values are filtered as per this and some of the values are showing incorrect for completed items.

Labels:
Message 1 of 10
1,373 Views
0
1 ACCEPTED SOLUTION
Anonymous
Not applicable

‎08-07-2022 06:29 AM

Yes I tried Userelationship, however it is not working, below is the sample dataset. could you please help me to bring total count of created items & completed items in column chart

 

Work Item IdStateWork Item TypeCreated DateCompleted DateWeek Created dateWeek Completion Start
12553DoneTask23-05-202216-06-202223-May13-Jun
12558DoneTask23-05-202207-06-202223-May06-Jun
12559DoneTask06-06-202216-06-202206-Jun13-Jun
12563DoneBug04-02-202215-06-202231-Jan13-Jun
12564DoneBug02-08-202205-08-202201-Aug01-Aug
12571DoneSpike02-06-202105-07-202131-May05-Jul
12572DoneSpike24-06-202128-06-202121-Jun28-Jun
12573DoneSpike03-06-202116-06-202131-May14-Jun
12574DoneProduct Backlog Item23-04-202115-07-202119-Apr12-Jul
12578DoneSpike12-07-202128-07-202112-Jul26-Jul
12579DoneProduct Backlog Item23-10-202019-07-202119-Oct19-Jul
12580DoneSpike23-06-202105-07-202121-Jun05-Jul
12586DoneSpike14-07-202130-07-202112-Jul26-Jul
12602DoneSpike20-06-202230-07-202220-Jun25-Jul
12660ClosedTest Case01-09-202003-09-202031-Aug31-Aug
12661ClosedTest Case01-09-202003-09-202031-Aug31-Aug
12662ClosedTest Case01-09-202003-09-202031-Aug31-Aug
12663ClosedTest Case01-09-202003-09-202031-Aug31-Aug
12664ClosedTest Case01-09-202003-09-202031-Aug31-Aug
12676ClosedTest Case25-05-202124-06-202124-May21-Jun
12680ClosedTest Case13-07-202113-07-202112-Jul12-Jul
12681ClosedTest Case05-05-202109-07-202103-May05-Jul
12685ClosedTest Case05-05-202109-07-202103-May05-Jul
12686ClosedTest Case25-05-202124-06-202124-May21-Jun
12688ClosedTest Case25-05-202124-06-202124-May21-Jun
12689ClosedTest Case14-07-202114-07-202112-Jul12-Jul
12694ClosedTest Case14-07-202114-07-202112-Jul12-Jul
12695ClosedTest Case25-05-202124-06-202124-May21-Jun
12696ClosedTest Case14-07-202114-07-202112-Jul12-Jul
12697ClosedTest Case02-07-202107-07-202128-Jun05-Jul
12698ClosedTest Case10-06-202111-06-202107-Jun07-Jun
12705ClosedTest Case13-07-202104-08-202112-Jul02-Aug
12708ClosedTest Case14-07-202104-08-202112-Jul02-Aug
12709ClosedTest Case17-08-202126-08-202116-Aug23-Aug
12710ClosedTest Case18-08-202127-08-202116-Aug23-Aug
12713ClosedTest Case02-08-202103-08-202102-Aug02-Aug
12714ClosedTest Case18-08-202127-08-202116-Aug23-Aug
12715ClosedTest Case29-07-202130-07-202126-Jul26-Jul
12716ClosedTest Case27-08-202127-08-202123-Aug23-Aug
12718ClosedTest Case27-08-202127-08-202123-Aug23-Aug
12721ClosedTest Case14-07-202104-08-202112-Jul02-Aug
12722ClosedTest Case17-08-202127-08-202116-Aug23-Aug
12746ClosedTest Case20-06-202220-06-202220-Jun20-Jun
12748ClosedTest Case28-06-202228-06-202227-Jun27-Jun
12756ClosedTest Case21-06-202221-06-202220-Jun20-Jun
12761ClosedTest Case20-07-202220-07-202218-Jul18-Jul
12762ClosedTest Case19-07-202219-07-202218-Jul18-Jul
12763ClosedTest Case27-07-202227-07-202225-Jul25-Jul
12773DoneSpike13-05-202101-07-202110-May28-Jun
12775DoneBug08-06-202116-08-202107-Jun16-Aug
12776DoneSpike17-06-202128-06-202114-Jun28-Jun
12781DoneBug08-06-202116-08-202107-Jun16-Aug
12783DoneBug07-06-202116-08-202107-Jun16-Aug
12789DoneBug09-06-202122-06-202107-Jun21-Jun
12799DoneBug16-06-202112-07-202214-Jun11-Jul
12800DoneBug16-06-202105-07-202214-Jun04-Jul
12808DoneBug13-01-202230-06-202210-Jan27-Jun
12809DoneBug23-06-202230-06-202220-Jun27-Jun
12810DoneBug27-06-202230-06-202227-Jun27-Jun
12811DoneBug24-06-202227-06-202220-Jun27-Jun
12812DoneBug31-05-202229-06-202230-May27-Jun
12813DoneBug23-02-202229-06-202221-Feb27-Jun
12815DoneBug27-06-202201-07-202227-Jun27-Jun

View solution in original post

Message 6 of 10
1,197 Views
0
9 REPLIES 9
Anonymous
Not applicable

‎08-10-2022 08:53 AM

Thank you so much for the response, it is working fine, On top of this I need one more help on Cumulative total for every weekend, I have created a separate table as summarized, however when I use the values in column chart and select the date range as last 2 months, i am not seeing the exact values as it is cutting the values. could you please help me on this

Message 10 of 10
1,129 Views
0
Hoangechip910
Frequent Visitor

‎08-07-2022 07:07 AM

Hi 

use USERELATIONSHIP as suggest is a good choice. You create a new Date table with fied name "Date", create relation field CreateDate with Date, then use USERRELATIONSHIP as
total = CALCULATE(SUM(),USERELATIONSHIP(column of "Date", column of "Complete Date))

 

But as Microsoft instruction, USERELATIONSHIP  have limitation if you define Row Level Security in your table.

 

For me, make it simple, I will create a new table of "Complete", include product and completedate, that mean you will have one table about createdate, one about complete date, 2 table related with an Date table, then you can filter as you want

Message 9 of 10
1,250 Views
0
Anonymous
Not applicable

‎08-07-2022 06:30 AM

X axis should be week start date, I tried using common date table, it is not working. Please also guide which common date i should use in Slicer

Message 8 of 10
1,263 Views
0
Anonymous
Not applicable

‎08-07-2022 06:29 AM

Yes I tried Userelationship, however it is not working, below is the sample dataset. could you please help me to bring total count of created items & completed items in column chart

 

Work Item IdStateWork Item TypeCreated DateCompleted DateWeek Created dateWeek Completion Start
12553DoneTask23-05-202216-06-202223-May13-Jun
12558DoneTask23-05-202207-06-202223-May06-Jun
12559DoneTask06-06-202216-06-202206-Jun13-Jun
12563DoneBug04-02-202215-06-202231-Jan13-Jun
12564DoneBug02-08-202205-08-202201-Aug01-Aug
12571DoneSpike02-06-202105-07-202131-May05-Jul
12572DoneSpike24-06-202128-06-202121-Jun28-Jun
12573DoneSpike03-06-202116-06-202131-May14-Jun
12574DoneProduct Backlog Item23-04-202115-07-202119-Apr12-Jul
12578DoneSpike12-07-202128-07-202112-Jul26-Jul
12579DoneProduct Backlog Item23-10-202019-07-202119-Oct19-Jul
12580DoneSpike23-06-202105-07-202121-Jun05-Jul
12586DoneSpike14-07-202130-07-202112-Jul26-Jul
12602DoneSpike20-06-202230-07-202220-Jun25-Jul
12660ClosedTest Case01-09-202003-09-202031-Aug31-Aug
12661ClosedTest Case01-09-202003-09-202031-Aug31-Aug
12662ClosedTest Case01-09-202003-09-202031-Aug31-Aug
12663ClosedTest Case01-09-202003-09-202031-Aug31-Aug
12664ClosedTest Case01-09-202003-09-202031-Aug31-Aug
12676ClosedTest Case25-05-202124-06-202124-May21-Jun
12680ClosedTest Case13-07-202113-07-202112-Jul12-Jul
12681ClosedTest Case05-05-202109-07-202103-May05-Jul
12685ClosedTest Case05-05-202109-07-202103-May05-Jul
12686ClosedTest Case25-05-202124-06-202124-May21-Jun
12688ClosedTest Case25-05-202124-06-202124-May21-Jun
12689ClosedTest Case14-07-202114-07-202112-Jul12-Jul
12694ClosedTest Case14-07-202114-07-202112-Jul12-Jul
12695ClosedTest Case25-05-202124-06-202124-May21-Jun
12696ClosedTest Case14-07-202114-07-202112-Jul12-Jul
12697ClosedTest Case02-07-202107-07-202128-Jun05-Jul
12698ClosedTest Case10-06-202111-06-202107-Jun07-Jun
12705ClosedTest Case13-07-202104-08-202112-Jul02-Aug
12708ClosedTest Case14-07-202104-08-202112-Jul02-Aug
12709ClosedTest Case17-08-202126-08-202116-Aug23-Aug
12710ClosedTest Case18-08-202127-08-202116-Aug23-Aug
12713ClosedTest Case02-08-202103-08-202102-Aug02-Aug
12714ClosedTest Case18-08-202127-08-202116-Aug23-Aug
12715ClosedTest Case29-07-202130-07-202126-Jul26-Jul
12716ClosedTest Case27-08-202127-08-202123-Aug23-Aug
12718ClosedTest Case27-08-202127-08-202123-Aug23-Aug
12721ClosedTest Case14-07-202104-08-202112-Jul02-Aug
12722ClosedTest Case17-08-202127-08-202116-Aug23-Aug
12746ClosedTest Case20-06-202220-06-202220-Jun20-Jun
12748ClosedTest Case28-06-202228-06-202227-Jun27-Jun
12756ClosedTest Case21-06-202221-06-202220-Jun20-Jun
12761ClosedTest Case20-07-202220-07-202218-Jul18-Jul
12762ClosedTest Case19-07-202219-07-202218-Jul18-Jul
12763ClosedTest Case27-07-202227-07-202225-Jul25-Jul
12773DoneSpike13-05-202101-07-202110-May28-Jun
12775DoneBug08-06-202116-08-202107-Jun16-Aug
12776DoneSpike17-06-202128-06-202114-Jun28-Jun
12781DoneBug08-06-202116-08-202107-Jun16-Aug
12783DoneBug07-06-202116-08-202107-Jun16-Aug
12789DoneBug09-06-202122-06-202107-Jun21-Jun
12799DoneBug16-06-202112-07-202214-Jun11-Jul
12800DoneBug16-06-202105-07-202214-Jun04-Jul
12808DoneBug13-01-202230-06-202210-Jan27-Jun
12809DoneBug23-06-202230-06-202220-Jun27-Jun
12810DoneBug27-06-202230-06-202227-Jun27-Jun
12811DoneBug24-06-202227-06-202220-Jun27-Jun
12812DoneBug31-05-202229-06-202230-May27-Jun
12813DoneBug23-02-202229-06-202221-Feb27-Jun
12815DoneBug27-06-202201-07-202227-Jun27-Jun
Message 6 of 10
1,198 Views
0
danextian
Super User
Super User
In response to Anonymous

‎08-10-2022 06:53 PM

Hi @Anonymous ,

Just tested using your sample data and the USERELATIONSHIP result is okay on my end.

For this function to work, a column that's related to the other two date columns. Using either one of the two will not help you achieve your desired result.

Here's a screenshot of the result:

danextian_0-1660182721844.png

pbix for your reference - https://drive.google.com/file/d/1UU-5U31jJanlNXBRRNC3SUBo_hWfuxS7/view?usp=sharing 

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Message 7 of 10
1,118 Views
0
Anonymous
Not applicable

‎08-07-2022 02:58 AM

Hi Dane,

 

Thank you for your reply, the problem here is the completed date is filtered out by created date. For ex: for the weekend starting 25 Jul 2022, the created number is 20(items created between 25th Jul to 31st Jul) similarly the completed items should show 25(Items completed between 25th Jul to 31st Jul). but the problem here is the completed items is considering only the items which got created on the above weekend only, but the requirement here is any items which got completed irrespective of the creation date must show. But unfortunately it is cutting the numbers. so it should not filter the created date . Can you please suggest

RKR25_0-1659866323650.png

 

 

Message 3 of 10
1,320 Views
0
danextian
Super User
Super User
In response to Anonymous

‎08-07-2022 05:13 AM

Have you tried using USERELATIONSHIP as suggested? The sample is in the pbix I attached. USERELATIONSHIP should allow an inactive relationship to be used in the measure so it doesn't get filtered by the other active relationship using the column/field from another  table.  If this doesn't work, please post a sample pbix with sanitized data.

 

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/2607381#M9185... 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Message 4 of 10
1,285 Views
0
Anonymous
Not applicable
In response to danextian

‎08-07-2022 06:18 AM

Hi,

 

I am unable to attach my PBIX file, however below is the sample data, where I need to show Total count of Created items & Completed Items in column chart. As per your advise, I have used common date table and Userelationship However it is still not working. Could you please help me to resolve. Very urgent for me. Please. When I use created date in X axis it is cutting the completed items.  Also please guide me which date I should use in common slicer to filter the Weekday .

 

Work Item IdStateWork Item TypeCreated DateCompleted DateWeek Created dateWeek Completion Start
12553DoneTask23-05-202216-06-202223-May13-Jun
12558DoneTask23-05-202207-06-202223-May06-Jun
12559DoneTask06-06-202216-06-202206-Jun13-Jun
12563DoneBug04-02-202215-06-202231-Jan13-Jun
12564DoneBug02-08-202205-08-202201-Aug01-Aug
12571DoneSpike02-06-202105-07-202131-May05-Jul
12572DoneSpike24-06-202128-06-202121-Jun28-Jun
12573DoneSpike03-06-202116-06-202131-May14-Jun
12574DoneProduct Backlog Item23-04-202115-07-202119-Apr12-Jul
12578DoneSpike12-07-202128-07-202112-Jul26-Jul
12579DoneProduct Backlog Item23-10-202019-07-202119-Oct19-Jul
12580DoneSpike23-06-202105-07-202121-Jun05-Jul
12586DoneSpike14-07-202130-07-202112-Jul26-Jul
12602DoneSpike20-06-202230-07-202220-Jun25-Jul
12660ClosedTest Case01-09-202003-09-202031-Aug31-Aug
12661ClosedTest Case01-09-202003-09-202031-Aug31-Aug
12662ClosedTest Case01-09-202003-09-202031-Aug31-Aug
12663ClosedTest Case01-09-202003-09-202031-Aug31-Aug
12664ClosedTest Case01-09-202003-09-202031-Aug31-Aug
12676ClosedTest Case25-05-202124-06-202124-May21-Jun
12680ClosedTest Case13-07-202113-07-202112-Jul12-Jul
12681ClosedTest Case05-05-202109-07-202103-May05-Jul
12685ClosedTest Case05-05-202109-07-202103-May05-Jul
12686ClosedTest Case25-05-202124-06-202124-May21-Jun
12688ClosedTest Case25-05-202124-06-202124-May21-Jun
12689ClosedTest Case14-07-202114-07-202112-Jul12-Jul
12694ClosedTest Case14-07-202114-07-202112-Jul12-Jul
12695ClosedTest Case25-05-202124-06-202124-May21-Jun
12696ClosedTest Case14-07-202114-07-202112-Jul12-Jul
12697ClosedTest Case02-07-202107-07-202128-Jun05-Jul
12698ClosedTest Case10-06-202111-06-202107-Jun07-Jun
12705ClosedTest Case13-07-202104-08-202112-Jul02-Aug
12708ClosedTest Case14-07-202104-08-202112-Jul02-Aug
12709ClosedTest Case17-08-202126-08-202116-Aug23-Aug
12710ClosedTest Case18-08-202127-08-202116-Aug23-Aug
12713ClosedTest Case02-08-202103-08-202102-Aug02-Aug
12714ClosedTest Case18-08-202127-08-202116-Aug23-Aug
12715ClosedTest Case29-07-202130-07-202126-Jul26-Jul
12716ClosedTest Case27-08-202127-08-202123-Aug23-Aug
12718ClosedTest Case27-08-202127-08-202123-Aug23-Aug
12721ClosedTest Case14-07-202104-08-202112-Jul02-Aug
12722ClosedTest Case17-08-202127-08-202116-Aug23-Aug
12746ClosedTest Case20-06-202220-06-202220-Jun20-Jun
12748ClosedTest Case28-06-202228-06-202227-Jun27-Jun
12756ClosedTest Case21-06-202221-06-202220-Jun20-Jun
12761ClosedTest Case20-07-202220-07-202218-Jul18-Jul
12762ClosedTest Case19-07-202219-07-202218-Jul18-Jul
12763ClosedTest Case27-07-202227-07-202225-Jul25-Jul
12773DoneSpike13-05-202101-07-202110-May28-Jun
12775DoneBug08-06-202116-08-202107-Jun16-Aug
12776DoneSpike17-06-202128-06-202114-Jun28-Jun
12781DoneBug08-06-202116-08-202107-Jun16-Aug
12783DoneBug07-06-202116-08-202107-Jun16-Aug
12789DoneBug09-06-202122-06-202107-Jun21-Jun
12799DoneBug16-06-202112-07-202214-Jun11-Jul
12800DoneBug16-06-202105-07-202214-Jun04-Jul
12808DoneBug13-01-202230-06-202210-Jan27-Jun
12809DoneBug23-06-202230-06-202220-Jun27-Jun
12810DoneBug27-06-202230-06-202227-Jun27-Jun
12811DoneBug24-06-202227-06-202220-Jun27-Jun
12812DoneBug31-05-202229-06-202230-May27-Jun
12813DoneBug23-02-202229-06-202221-Feb27-Jun
12815DoneBug27-06-202201-07-202227-Jun27-Jun
Message 5 of 10
1,158 Views
0
danextian
Super User
Super User

‎08-07-2022 02:43 AM

Hi @Anonymous ,

 

For this, I'd use a separate Datest able and create a relationship to Created Date and Complete Date columns with one being inactive. I would then use USERELATIONSHIP function to make use of an inactive relationship.

active and inactive (in dashes) relationships

danextian_0-1659865052376.png

sample formula

Total (USERELATIONSHIP) = 
CALCULATE ( [Total], USERELATIONSHIP ( Dates[Date], Data[Completed Date] ) )

 

sample result

danextian_1-1659865382744.png

 

Please see this pbix for your reference - https://drive.google.com/file/d/1fig8KTnWDnzL1ZkVjKUGmv9TYlE7xyZ8/view?usp=sharing 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Message 2 of 10
1,322 Views
0

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All