cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kevpuk Frequent Visitor
Frequent Visitor

Dynamic measure, based on period?

Hi All

 

So, I have been struggling with how to resolve a problem.

I have a set of data, made up of individual measures on rows, with period in columns (2015-2019)......what I am trying to do is make the value in the 2019 column be a projected/annualised value, rather than the actual 2019 value. This is because some of the data in 2019 is incomplete/placeholder/estimate, so I want 2019 column value to be a refined calculated value instead.

 

Data table is similar to this:

Capture.PNG......to further complicate, possibly, the rows marked as 'rate' - e.g. Metric 7 Rate - are calculated baseed on other rows' values.

 

Appreciate any help/guidance Smiley Happy

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Dynamic measure, based on period?

Hi @kevpuk,

Could you mean to calculate the [Fall] and [Trip] measure with sample data for 2016, 2017, 2018 and use another measure for 2019?

If so, could you have tried with IF function?

Fall = IF(CALCULATE(MAX('sample_data'[Period])<>"2019"),calculate(Sum(sample_data[Value]),sample_data[Metric]="Fall"),0)

(You could replace the 0 in the formula to the measure of calculating "2019")

1.PNG

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Community Support Team
Community Support Team

Re: Dynamic measure, based on period?

Hi @kevpuk,

Could you please offer your sample file to have a test and post your desired result if possible?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
kevpuk Frequent Visitor
Frequent Visitor

Re: Dynamic measure, based on period?

Hi

 

Thanks for your response.

 

I have created a simple sampl .pbix file......on the left hand side of the page are simple matrix visuals, with annual data from 2016-2019......to the right, with red borders around, I have created a couple of simple matrix visuals using measures to provide an 'annualised' view of the current year (simply, item count/days in year * 365).

 

https://1drv.ms/u/s!AqSWpWAD-qDBgu1E5Lif9Ne6lMwuvg

 

What I am trying to achieve is a method to make one visual that - in this case - would show actual numbers for 2016, 2017 and 2018, but would show the annualised value for 2019.

 

Thanks for taking the time to look at this Smiley Happy

Community Support Team
Community Support Team

Re: Dynamic measure, based on period?

Hi @kevpuk,

Could you mean to calculate the [Fall] and [Trip] measure with sample data for 2016, 2017, 2018 and use another measure for 2019?

If so, could you have tried with IF function?

Fall = IF(CALCULATE(MAX('sample_data'[Period])<>"2019"),calculate(Sum(sample_data[Value]),sample_data[Metric]="Fall"),0)

(You could replace the 0 in the formula to the measure of calculating "2019")

1.PNG

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
kevpuk Frequent Visitor
Frequent Visitor

Re: Dynamic measure, based on period?

Hi Daniel

 

Well, that is elegant, nice and simple - thank-you very much Smiley Happy