cancel
Showing results for
Did you mean:
Helper II

Get even total average across months

Hi there,

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".

I just need the average from the total evenly spread across the months starting from the start date for instance, please see result below.

Data:

 product total_amt start_date end_date Months a 100 4/1/2020 3/31/2021 12 b 200 1/1/2020 12/31/2020 12 c 300 2/1/2020 1/31/2021 12

Expected Result:

 product total_amt start_date end_date Months Jan-2020 feb-2020 march-2020 April-2020 May-2020 jun-2020 Jul-2020 Aug-2020 Sept-2020 Oct-2020 Nov-2020 Dec-2020 jan-2021 feb-2021 March-2021 a 100 4/1/2020 3/31/2021 12 8.3 8.3 8.3 8.3 8.3 8.3 8.3 8.3 8.3 8.3 8.3 8.3 b 200 1/1/2020 12/31/2020 12 16.7 16.7 16.7 16.7 16.7 16.7 16.7 16.7 16.7 16.7 16.7 16.7 c 300 2/1/2020 1/31/2021 12 25 25 25 25 25 25 25 25 25 25 25 25
1 ACCEPTED SOLUTION
Super User

Hi,

Please check the below picture and the attached pbix file.

``````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
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

2 REPLIES 2
Super User

Hi,

Please check the below picture and the attached pbix file.

``````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
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Helper II

@Jihwan_Kim 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.

Announcements