Get even total average across months

Question

Hi there,&nbsp;I need help with getting an even average across the calendar months counting from the start date adding to 12 months. I have a date table connected to my data table through the "start_date".&nbsp;I just need the average from the total evenly spread across the months starting from the start date for instance, please see result below.&nbsp;Thank you in advance for your help.&nbsp;Data:producttotal_amtstart_dateend_dateMonthsa1004/1/20203/31/202112b2001/1/202012/31/202012c3002/1/20201/31/202112&nbsp;Expected Result:producttotal_amtstart_dateend_dateMonthsJan-2020feb-2020march-2020April-2020May-2020jun-2020Jul-2020Aug-2020Sept-2020Oct-2020Nov-2020Dec-2020jan-2021feb-2021March-2021a1004/1/20203/31/202112&nbsp;&nbsp;&nbsp;8.38.38.38.38.38.38.38.38.38.38.38.3b2001/1/202012/31/20201216.716.716.716.716.716.716.716.716.716.716.716.7&nbsp;&nbsp;&nbsp;c3002/1/20201/31/202112&nbsp;252525252525252525252525&nbsp;&nbsp;

jihwan_kim · Accepted Answer

Hi,
Please check the below picture and the attached pbix file.

&nbsp;
Expected result measure: =
VAR monthscount =
    COUNTROWS (
        SUMMARIZE (
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Date] &gt;= MAX ( Data[start_date] )
                    &amp;&amp; 'Calendar'[Date] &lt;= MAX ( Data[end_date] )
            ),
            'Calendar'[Month &amp; Year]
        )
    )
VAR totalamount =
    SUM ( Data[total_amt] )
VAR result =
    DIVIDE ( totalamount, monthscount )
RETURN
    IF (
        MIN ( 'Calendar'[Date] ) &gt;= MAX ( Data[start_date] )
            &amp;&amp; MAX ( 'Calendar'[Date] ) &lt;= MAX ( Data[end_date] ),
        result
    )

Forum Discussion

Related content

Total per month / average per month

Calculating average 12 month average

Dynamic Average per month Calculation

Average for Month Excluding Current Month

Weighted average by month