Calculate the average of last 6 months is data is blank

[luismolle]

Hello!

Im trying to build a dashboard where I have a measure and if the date is in the future (blank value) the measure should do the average of last six months. Can you help me?

Example: In the image, 'July' should sum the values since January and divide by 6.

thanks.

Luis.

[parry2k]

@luismolle It is possible, if you can share your pbix file (remove sensitive information) and explain the output, I will get you the solution.

[luismolle]

Hi parry2k,

I found a possible easier way to solve my demand here. Can you take a look into the following link for the other question I opened, please? 

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-show-value-from-last-year...

[parry2k]

@luismolle If you had answered my question you would had a solution by now. Good luck!

[Ashish_Mathur]

Hi,

What should the result of August be?

[luismolle]

Hello! Should be the sum between February and July, divided by 6.
In this case, July should result 399,553,108.5 and August should result 310,643,190.91

[Ashish_Mathur · Accepted solution]

Hi,

I don't think this is possible with a DAX measure because this will lead to a circular reference. 

[parry2k]

@luismolle first what defines future months? Any month after today()? 2nd, does it use average for all future months or just the next month after today()?

[luismolle]

Im working with Fiscal Years, so I will always have the full year months as an estimate and, since the months are being completed, I will change the value to actuals.