Average calculation

[vasathi]

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 strugling to get this in days but no idea how to proceed.

Advance Thanks 

[VijayP]

@vasathi 

I have created new table tried working on the average , but still I am not convinced with the Logic 

New Table = FILTER(

SUMMARIZE(dimDate,dimDate[Date],"BCOGS",BudgetAvgTrans[Total BCOGS],"AVGCOGS",SUM(BudgetAvgTrans[AVGCOGS])),not(ISBLANK(SUM(BudgetTrans[BCOGS]))) && NOT(ISBLANK(SUM(BudgetAvgTrans[AVGCOGS]))))

Check the file attached and let me know! Share your Kudos

[vasathi]

@VijayP 

i saw your solutions, but you created one calculated column and year was fixed, i don't want to year to be fixed here

Also, i'm not able to filters with other dimensions like company, brand and location etc...

i hope you can help me

[VijayP]

@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]

@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

[vasathi]

Team

Sample data link shared

Sample Data 

below output is required

thanks

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523