Forum Discussion

vasathi's avatar
vasathi
Helper II
4 years ago

Average calculation

Team,   I have a requirment of calculating average balances from two columns in same table looks like below my sample data Could you someone help me to get this report out i strug...
Need Help
VijayP's avatar
VijayP
Community Champion
4 years ago

vasathi 

Questions - What is the reason Aug is left over, What is the logic to consider respective cells ! without Logic we may not be able to build the DAX. 
For a regular AVERAGE you can use this function but further logic you need to define and let me know to give you more clear DAX

average = IF(NOT(ISBLANK([Total BCOGS])),
AVERAGEX(DATESINPERIOD(dimDate[Date],SELECTEDVALUE(dimDate[Date]),6,MONTH),[Total BCOGS]),BLANK())
vasathi's avatar
vasathi
Helper II
4 years ago

VijayP 

The actual requirement is, Average COGS will available only for Jan-Jun for every year,

Average calculation is needed for future 6 months, if period is less than 6 months then remaining months need to take from average COGS column

Eg. For Jan - The average values from Feb-July
for Feb - the average values from Mar-Aug
....
for Jul - The average values from Aug-Dec - 5 Months and Jan month AVGCOG for 1 month
For Aug  - The average values from Sep-Jan - 5 Months and Feb month AVGCOG for 1 month 
...
Dec The Average values are Jan-May for 5 months and  Jun month AVGCOGS for 1 month

i hope explained your query