topic Get even total average across months in DAX Commands and Tips
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Get-even-total-average-across-months/m-p/2441608#M65400
<P>Hi there,</P><P> </P><P>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".</P><P> </P><P>I just need the average from the total evenly spread across the months starting from the start date for instance, please see result below.</P><P> </P><P>Thank you in advance for your help.</P><P> </P><P>Data:</P><TABLE border="1"><TBODY><TR><TD>product</TD><TD>total_amt</TD><TD>start_date</TD><TD>end_date</TD><TD>Months</TD></TR><TR><TD>a</TD><TD>100</TD><TD>4/1/2020</TD><TD>3/31/2021</TD><TD>12</TD></TR><TR><TD>b</TD><TD>200</TD><TD>1/1/2020</TD><TD>12/31/2020</TD><TD>12</TD></TR><TR><TD>c</TD><TD>300</TD><TD>2/1/2020</TD><TD>1/31/2021</TD><TD>12</TD></TR></TBODY></TABLE><P> </P><P>Expected Result:</P><TABLE border="1"><TBODY><TR><TD>product</TD><TD>total_amt</TD><TD>start_date</TD><TD>end_date</TD><TD>Months</TD><TD>Jan-2020</TD><TD>feb-2020</TD><TD>march-2020</TD><TD>April-2020</TD><TD>May-2020</TD><TD>jun-2020</TD><TD>Jul-2020</TD><TD>Aug-2020</TD><TD>Sept-2020</TD><TD>Oct-2020</TD><TD>Nov-2020</TD><TD>Dec-2020</TD><TD>jan-2021</TD><TD>feb-2021</TD><TD>March-2021</TD></TR><TR><TD>a</TD><TD>100</TD><TD>4/1/2020</TD><TD>3/31/2021</TD><TD>12</TD><TD> </TD><TD> </TD><TD> </TD><TD>8.3</TD><TD>8.3</TD><TD>8.3</TD><TD>8.3</TD><TD>8.3</TD><TD>8.3</TD><TD>8.3</TD><TD>8.3</TD><TD>8.3</TD><TD>8.3</TD><TD>8.3</TD><TD>8.3</TD></TR><TR><TD>b</TD><TD>200</TD><TD>1/1/2020</TD><TD>12/31/2020</TD><TD>12</TD><TD>16.7</TD><TD>16.7</TD><TD>16.7</TD><TD>16.7</TD><TD>16.7</TD><TD>16.7</TD><TD>16.7</TD><TD>16.7</TD><TD>16.7</TD><TD>16.7</TD><TD>16.7</TD><TD>16.7</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>c</TD><TD>300</TD><TD>2/1/2020</TD><TD>1/31/2021</TD><TD>12</TD><TD> </TD><TD>25</TD><TD>25</TD><TD>25</TD><TD>25</TD><TD>25</TD><TD>25</TD><TD>25</TD><TD>25</TD><TD>25</TD><TD>25</TD><TD>25</TD><TD>25</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>Wed, 06 Apr 2022 20:21:39 GMTyve2142022-04-06T20:21:39ZGet even total average across months
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Get-even-total-average-across-months/m-p/2441608#M65400
<P>Hi there,</P><P> </P><P>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".</P><P> </P><P>I just need the average from the total evenly spread across the months starting from the start date for instance, please see result below.</P><P> </P><P>Thank you in advance for your help.</P><P> </P><P>Data:</P><TABLE border="1"><TBODY><TR><TD>product</TD><TD>total_amt</TD><TD>start_date</TD><TD>end_date</TD><TD>Months</TD></TR><TR><TD>a</TD><TD>100</TD><TD>4/1/2020</TD><TD>3/31/2021</TD><TD>12</TD></TR><TR><TD>b</TD><TD>200</TD><TD>1/1/2020</TD><TD>12/31/2020</TD><TD>12</TD></TR><TR><TD>c</TD><TD>300</TD><TD>2/1/2020</TD><TD>1/31/2021</TD><TD>12</TD></TR></TBODY></TABLE><P> </P><P>Expected Result:</P><TABLE border="1"><TBODY><TR><TD>product</TD><TD>total_amt</TD><TD>start_date</TD><TD>end_date</TD><TD>Months</TD><TD>Jan-2020</TD><TD>feb-2020</TD><TD>march-2020</TD><TD>April-2020</TD><TD>May-2020</TD><TD>jun-2020</TD><TD>Jul-2020</TD><TD>Aug-2020</TD><TD>Sept-2020</TD><TD>Oct-2020</TD><TD>Nov-2020</TD><TD>Dec-2020</TD><TD>jan-2021</TD><TD>feb-2021</TD><TD>March-2021</TD></TR><TR><TD>a</TD><TD>100</TD><TD>4/1/2020</TD><TD>3/31/2021</TD><TD>12</TD><TD> </TD><TD> </TD><TD> </TD><TD>8.3</TD><TD>8.3</TD><TD>8.3</TD><TD>8.3</TD><TD>8.3</TD><TD>8.3</TD><TD>8.3</TD><TD>8.3</TD><TD>8.3</TD><TD>8.3</TD><TD>8.3</TD><TD>8.3</TD></TR><TR><TD>b</TD><TD>200</TD><TD>1/1/2020</TD><TD>12/31/2020</TD><TD>12</TD><TD>16.7</TD><TD>16.7</TD><TD>16.7</TD><TD>16.7</TD><TD>16.7</TD><TD>16.7</TD><TD>16.7</TD><TD>16.7</TD><TD>16.7</TD><TD>16.7</TD><TD>16.7</TD><TD>16.7</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>c</TD><TD>300</TD><TD>2/1/2020</TD><TD>1/31/2021</TD><TD>12</TD><TD> </TD><TD>25</TD><TD>25</TD><TD>25</TD><TD>25</TD><TD>25</TD><TD>25</TD><TD>25</TD><TD>25</TD><TD>25</TD><TD>25</TD><TD>25</TD><TD>25</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>Wed, 06 Apr 2022 20:21:39 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Get-even-total-average-across-months/m-p/2441608#M65400yve2142022-04-06T20:21:39ZRe: Get even total average across months
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Get-even-total-average-across-months/m-p/2441990#M65434
<P>Hi,</P>
<P>Please check the below picture and the attached pbix file.</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Picture2.png" style="width: 999px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/696046iC4D34A48E06DA0AB/image-size/large?v=v2&px=999" role="button" title="Picture2.png" alt="Picture2.png" /></span></P>
<P> </P>
<LI-CODE lang="markup">Expected result measure: =
VAR monthscount =
COUNTROWS (
SUMMARIZE (
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Date] >= MAX ( Data[start_date] )
&& 'Calendar'[Date] <= MAX ( Data[end_date] )
),
'Calendar'[Month & Year]
)
)
VAR totalamount =
SUM ( Data[total_amt] )
VAR result =
DIVIDE ( totalamount, monthscount )
RETURN
IF (
MIN ( 'Calendar'[Date] ) >= MAX ( Data[start_date] )
&& MAX ( 'Calendar'[Date] ) <= MAX ( Data[end_date] ),
result
)
</LI-CODE>Thu, 07 Apr 2022 03:03:00 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Get-even-total-average-across-months/m-p/2441990#M65434Jihwan_Kim2022-04-07T03:03:00ZRe: Get even total average across months
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Get-even-total-average-across-months/m-p/2443851#M65525
<P><LI-USER uid="291628"></LI-USER> Thank you so much. I was able to get my variables similarly to the one you had but I made the mistake of conencting my tables to the data table. This worked perfectly, thank you.</P>Thu, 07 Apr 2022 15:03:12 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Get-even-total-average-across-months/m-p/2443851#M65525yve2142022-04-07T15:03:12Z