Forum Discussion
Combining dates from 2 columns based on unique ID
I have a table that shows the quantity of fruit recorded for a specific ID. In the same table, I have a forecasted date where we can assume the quantity of fruit is = 0. I'd like to create a new table that inserts the date from the "forecast empty" as an amount of 0, with the "date recorded" date.
This table will be used to create a line graph showing the previously recorded data and most recently forecasted date.
flyingmada - Sorry I mis-understood the requirement. Updated PBIX is attached.
Table (17) = VAR __BaseTable = ALL('Table (16)') VAR __Table = SELECTCOLUMNS('Table (16)',"Date",[Date Recorded],"Amount",[Amount],"Fruit",[Fruit],"Field",[Field]) VAR __Table1a = ADDCOLUMNS( GROUPBY( 'Table (16)',[Fruit],[Field], "Date",MAXX(CURRENTGROUP(),[Date Recorded]) ), "Forecast Empty",MAXX(FILTER(ALL('Table (16)'),'Table (16)'[Date Recorded]=EARLIER([Date]) && 'Table (16)'[Field]=EARLIER([Field]) && 'Table (16)'[Fruit]=EARLIER([Fruit])),[Forecast Empty]), "Amount",0 ) VAR __Table1 = SELECTCOLUMNS( __Table1a, "Date",[Forecast Empty], "Amount",[Amount], "Fruit",[Fruit], "Field",[Field] ) RETURN UNION(__Table,__Table1)
5 Replies
flyingmada - You can do it like this in DAX, there is also probably a Power Query solution. PBIX is attached, Table (16) and Table (17)
Table (17) = VAR __Table = SELECTCOLUMNS('Table (16)',"Date",[Date Recorded],"Amount",[Amount],"Fruit",[Fruit],"Field",[Field]) VAR __Table1 = SELECTCOLUMNS(ADDCOLUMNS(GROUPBY('Table (16)',[Fruit],[Field],"Date",MAXX(CURRENTGROUP(),[Forecast Empty])),"Amount",0),"Date",[Date],"Amount",[Amount],"Fruit",[Fruit],"Field",[Field]) RETURN UNION(__Table,__Table1)Greg_Deckler - This is really close but its returning the "latest" or "largest" of the forecasted dates, instead of the most recent (from date recorded) forecasted date. For example, table below:
flyingmada - Sorry I mis-understood the requirement. Updated PBIX is attached.
Table (17) = VAR __BaseTable = ALL('Table (16)') VAR __Table = SELECTCOLUMNS('Table (16)',"Date",[Date Recorded],"Amount",[Amount],"Fruit",[Fruit],"Field",[Field]) VAR __Table1a = ADDCOLUMNS( GROUPBY( 'Table (16)',[Fruit],[Field], "Date",MAXX(CURRENTGROUP(),[Date Recorded]) ), "Forecast Empty",MAXX(FILTER(ALL('Table (16)'),'Table (16)'[Date Recorded]=EARLIER([Date]) && 'Table (16)'[Field]=EARLIER([Field]) && 'Table (16)'[Fruit]=EARLIER([Fruit])),[Forecast Empty]), "Amount",0 ) VAR __Table1 = SELECTCOLUMNS( __Table1a, "Date",[Forecast Empty], "Amount",[Amount], "Fruit",[Fruit], "Field",[Field] ) RETURN UNION(__Table,__Table1)
flyingmada , Try a new table like
union (selectcolumns(Table,"Date", Table[Date recorded],"Amount", Table[Amount],"Fruit",Table[Fruit],"Field",Table[Field]),
selectcolumns(Table,"Date", Table[Date recorded],"Amount",0,"Fruit",Table[Fruit],"Field",Table[Field])
)refer
https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
amitchandak - this is also really close but its showing all of the forecasted dates in the table. Per my response to Greg, I only want to include the "forecast empty" date based on the most recent "date recorded" date.
