cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zhivana
Resolver II
Resolver II

Data with multiple snapshot refresh dates - calculate activity for previous refresh dates

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?
3 REPLIES 3
v-polly-msft
Community Support
Community Support

Hi @zhivana ,

Please have a try.

 

Measure = CALCUATE(MAX('TEST PLANTING'[Amount]),FILTER('TEST PLANTING'[Status]="Actual"&&'TEST PLANTING'[Plan ID]=planid&&'TEST PLANTING'[Refresh Date]<currentdate ))

 

If it does not work, please provide some  data.

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi Polly,


That did not work. I did however find a similar approach by testing a few options. This works as a calculated column.
Can you explain why the FILTER(ALL) makes it work?

Here is the working approach (calculated column, also works as measure)

 

var plantedpreviously =
CALCULATE(
MAX(
'TEST PLANTING'[Amount]),
FILTER(
ALL
('TEST PLANTING'),
'TEST PLANTING'[Status]="Actual" &&
'TEST PLANTING'[Plan ID]=planid &&
'TEST PLANTING'[Refresh Date]<currentdate
)
)

 

Hi @zhivana ,

Please try to learn the difference between column and measure.

Power BI Tutorial: When to Use Calculated Columns and Measures (insightsoftware.com)

There is an example about when to combine all and filter. Please refer to the document to see if it helps you.

https://foresightbi.com.ng/dax/when-to-combine-all-and-filter/

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!