Hi. i'm obviously doing something dim.
I have calculated column that sums the quantity on a table of invoices for different contracts to see how much has been consumed under each one. We want to be able to run this calculation at certain dates like EOM. Without any date filter it will always give the current balance not the one at EOM. Once the calculated column is correct then I calculate the value remaining on each contract and then have a measure that sums these quantities. This measure is in a pivot table.
Everything works fine when the date (the bit after <=) was set to a fixed date I put in a linked table and referenced in the formula, but that involved refreshing the linked table each time to change the date
I then thought I could give the user the ability to select the date using a disconnected slicer. i created a table of EOM Dates and linked them to the model. I created a slicer linked to the pivot and edited the calculated column as below, where PlatBSDate[Platinum BS Date] is the value chosen in the slicer.
However nothing happens when I change the slicer date. Is this because disconnected slicer can only work if the whole calculation is in a measure and can't affect calculated columns?
Thanks for any advice
Mike
I have calculated column that sums the quantity on a table of invoices for different contracts to see how much has been consumed under each one. We want to be able to run this calculation at certain dates like EOM. Without any date filter it will always give the current balance not the one at EOM. Once the calculated column is correct then I calculate the value remaining on each contract and then have a measure that sums these quantities. This measure is in a pivot table.
Everything works fine when the date (the bit after <=) was set to a fixed date I put in a linked table and referenced in the formula, but that involved refreshing the linked table each time to change the date
I then thought I could give the user the ability to select the date using a disconnected slicer. i created a table of EOM Dates and linked them to the model. I created a slicer linked to the pivot and edited the calculated column as below, where PlatBSDate[Platinum BS Date] is the value chosen in the slicer.
Code:
=CALCULATE( SUM('Posted Document Line'[Quantity]),
FILTER(
'Posted Document Line',
'Posted Document Line'[Contract line ID]='Contract_Service Discount'[Contract Line ID] &&
'Posted Document Line'[Posting Date]<=MAX(PlatBSDate[Platinum BS Date])
)
)
However nothing happens when I change the slicer date. Is this because disconnected slicer can only work if the whole calculation is in a measure and can't affect calculated columns?
Thanks for any advice
Mike