New column returning always the same value on a virtual table

[campelliann]

Hi there,

I have this table with project data, where each project lives on one row. I have several columns for each row, amongs them the beginning and ending date.

Table Sample

Project ID	Beginning Date (Projeccoes Data inicio in dax)	End date (produção in dax code below)
Project 1	1 jun	30 jun
Project 2	1 may	31 may

Expected Output

Project ID	Beginning Date (Projeccoes Data inicio in dax)	End date (produção in dax code below)	@Total days
Project 1	1 jun	30 jun	30
Project 2	1 may	31 may	31

Error I am getting: 

Project ID	Beginning Date (Projeccoes Data inicio in dax)	End date (produção in dax code below)	@Total days
Project 1	1 jun	30 jun	944
Project 2	1 may	31 may	944

So I basically need to create a virtual table where I add a new column with the total days of duration for each project. So I created this Dax code below, but when I go to check how the virtual table looks (creating a phyical table based on the code) I get the same duration for every project. It seems the code is not having a "row context" :

var current_project = SELECTEDVALUE('Project Table'[Project ID]) // current ID in the present row

var dates_project = calculate(countrows(DATESBETWEEN('Project Table'[Data Criação Project ID],SELECTCOLUMNS(filter('Project Table','Project Table'[Project ID]=current_project),"N",SELECTEDVALUE('Project Table'[Projecções Data Início])),SELECTCOLUMNS(filter('Project Table','Project Table'[Project ID]=current_project),"N2",SELECTEDVALUE('Project Table'[Produção])))))           // filters the initial table based on the current row project ID and then selects the Beginning date, and ending date. Finally I use countrows to count the number of days

var tabela_virtual = ADDCOLUMNS('Project Table',"@Total days",dates_project)))

Return tabela_virtual

[ValtteriN]

Hi,

Try this kind of DAX:

ADDCOLUMNS(Projects_v,"Time",DATEDIFF(Projects_v[Start],Projects_v[End],DAY)+1)

Example:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[campelliann]

Hi @ValtteriN. First and foremost thanks, I have been around this problem for a couple of days and this driving me crazy 🙂 Now I get only 1s in the last column. Note that I am trying to build a Virtual Table for a more complex problem. I just created this physical table to check how the virtual table looks. It seems that in the virtual table I am not creating a row by row count of the days... 

[Greg_Deckler · Accepted solution]

@campelliann I believe you want something like this:

Table =
  VAR __Project = SELECTEDVALUE('Triskall Complete'[Process])
  VAR __Table = 
    ADDCOLUMNS(
      FILTER('Triskall Complete',[Process] = __Project),
      "__Days",([End Date] - [Start Date]) + 0
    )
RETURN
  __Table

[ValtteriN]

Hi @campelliann 

I think the problem is with the SELECTEDVALUE. In the filter context you are applying it now it returns blank. 

You don't often need the intermediary var step in your virtual table.

E.G.

Datediff from virtual table = var _table = ADDCOLUMNS(Projects_v,"_Time",DATEDIFF(Projects_v[Start],Projects_v[End],DAY)+1) return

SUMX(_table,[_Time])

[Greg_Deckler]

@campelliann Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, 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

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[campelliann]

Hi @Greg_Deckler, I ve just edited the message. Hope is more clear now. Many thanks!