Good Morning,
The dataset for this is a live/snapshot model that shows the current status for particular sites (Plan IDs). When refreshed, the data shows the current planting amount and status (e.g. 100/scheduled means nothing yet planted, 100/actual means 100 planted).
Each month, we refresh the data then use DAX Studio to extract frozen CSV files, thus giving us a sequence of files that allow us to show change.
Now, for volume of planting, we need to calculate (1) the total amount planted as of the current date, and (2) the amount planted before the current date; the delta between the two is the planting in the current period.
Calculating (1) works fine:
Planted in Period =
var currentdate = 'TEST PLANTING'[Refresh Date]
var planid = 'TEST PLANTING'[Plan ID]
var everythingeverplanted =
CALCULATE(
MAX(
'TEST PLANTING'[Amount]),
'TEST PLANTING'[Status]="Actual",
'TEST PLANTING'[Plan ID]=planid
)
However, calculating (2) is problematic. My first attempt was:
var plantedpreviously =
CALCULATE(
MAX(
'TEST PLANTING'[Amount]),
'TEST PLANTING'[Status]="Actual",
'TEST PLANTING'[Plan ID]=planid,
'TEST PLANTING'[Refresh Date]<currentdate
)
This returns nulls (not zeroes, not errors). I have developed a workaround that works:
var filteredprevioustable =
FILTER(
'TEST PLANTING',
'TEST PLANTING'[Refresh Date]<currentdate
&&
'TEST PLANTING'[Plan ID]=planid
&&
'TEST PLANTING'[Status]="Actual"
)
var plantedprevioustable =
CALCULATE(
MAX(
'TEST PLANTING'[Amount]),
filteredprevioustable
)
Can anybody explain why my var plantedpreviously doesn't work? Is there a way I can rewrite it?
