I have a PBI Query as follows:
SELECTCOLUMNS(FILTER('WIP-S','WIP-S'[Period]<>"Current"),
"Period",SELECTCOLUMNS(FILTER('WIP-S','WIP-S'[Period]<>"Current"), DateAdd(MIN('WIP-S'[Period])),-7,day),"Commission", 'WIP-S'[Commission],
"Current", 'WIP-S'[Current],
"I1", 'WIP-S'[I1],
"P1", VALUE('WIP-S'[P1]),
"P2", VALUE('WIP-S'[P2]),
"P0", 'WIP-S'[P0],
"P4", 'WIP-S'[P4],
"P5", 'WIP-S'[P5],
"SM", 'WIP-S'[SM],
"WIP", 'WIP-S'[WIP],
"Territory",'WIP-S'[Territory]),
This data is imported from another system. The "Period" Column is Text, and contains text and "text dates".
I'm having an issue with the following line:
I'm having difficulty with this line:
"Period",SELECTCOLUMNS(FILTER('WIP-S','WIP-S'[Period]<>"Current"), DateAdd(MIN('WIP-S'[Period])),-7,day),
I'd like to get the minimum value of that table with the filter criteria, and then subtract 7 days from that minimum value and ensure it is converted to a date value.
A close approximation to what I am trying to accomplish in TSQL would be:
Select
I1,
Period=(Select DateAdd(day,-7,Min(Convert(date,[Period])) from [WIP-S] where [Period]<>'Current'),
P1,
P2,
P3,
P4,
P5,
SM,
WIP,
Territory
From [WIP-S]
How would I replicate the TSQL above to accomlish the result in PowerBI Query?
Thankyou.
