cumulative line based on figures of 31 December

[Anonymous]

Dear Community,

I have data figures in December 2020 and I want to sum it divide it on 12 and then represent the data as comulated line 

for example: 

Date:  Figures:

31/12/2020 25,000
31/12/2020 30,300

Sum = 55,300

Each month = 55,300 /12 =  4608,33

the chart should be comulated, 

the first value in the chart for the first month is 4608.33
the second value in the chart for the second month is 4608.33 * 2 = 9216.66

How can I do it?

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

We can create a new month table and a measure to meet your requirement.

1. Create a month table.

Table = SUMMARIZE(ADDCOLUMNS(CALENDAR("2020/1/1","2020/12/31"),"Month",MONTH([Date]),"Month_name",FORMAT([Date],"mmmm")),[Month],[Month_name])

2. Then we can create a measure to get the result.

Measure = SUM('Table (2)'[Figures]) /12 * MAX('Table'[Month])

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[amitchandak]

@Anonymous , one way it divide monthly or daily. And use it

https://www.dropbox.com/s/fnq82ksdzk1lqs3/Target_allocation_daily.pbix?dl=0

Other way is use https://docs.microsoft.com/en-us/dax/closingbalanceyear-function-dax

closingbalanceyear(Target[Target], Date[Date]) * month(Max(Date[date]))/12

when you view by month it will take the max dates month and divide ir by 12