LinkedIn
Twitter
Could someone let me know why the expression below is not producing the desired results?
Virtual Column = If( [BillingPeriod]=“10/1/2019”, Any(Select(invoicehistory[Previous],([InvoiceNumber]=[_ThisRow].[InvoiceNumber],[BillingPeriod]=“9/1/2019”) )),0)
I would like to retrieve [Previous] from the invoicehistory Table for the previous month for each Unique Row (InoviceNumber is Key) for the month of September 1 2019 and place it on a Virutal Column which will be used to compare values with the [Previous] Column for the month of October 1 2019.
On the below screenshot you can see that the result given by the above expression is 17,070 which is the value corresponding for the month of July 1 2019??? And then on the very last column (ie the Virtual Column) you do see the correct value for the month of September 1 2019 which is 17,169. However, the next value which should have been retrieved should be a different value. In other words, the result from the expression retrieves only the first row for the month of September 1 2019… but the 2nd, 3rd, 4th and nth rows values are not being retrieved. As you can see from the screenshot all the retrieved values are the same, 17,169.
