DAX Formula that will calculate what the end of the month Acquisition number will be based off of a

[lagerkrans1]

I want to use a DAX Formula that will calculate what the end of the month Acquisition number will be based off of a cumulative daily total.

data example below

date    acquisitions                cumulative acquisitions     end of month pace
1/1/2019    40                                      40                             1240
1/2/2019    48                                      88                              1364
1/3/2019    52                                     140                             1446
1/4/2019    39                                     178                            1379.5
1/5/2019    35                                      214                            1326
1/6/2019    45                                     259                             1338

I manually used the (cumulative acquisitions/day of the month)*(number of days in month-day of month)+ Cumulative acquisitions

ex for 1/1/2019 I did (40/1)*(31-1)+40

I want DAX to do this for me automatically

[v-juanli-msft · Accepted solution]

Hi @lagerkrans1

Create a calendar table 

calendar = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"Month",MONTH([Date]))

and create a calcuated column in this table

days = CALCULATE(COUNT('calendar'[Date]),ALLEXCEPT('calendar','calendar'[year],'calendar'[Month]))

create relationship between "calendar" and your data tab;e based on date

create measures in your data table

cumulative acquisitions = CALCULATE(SUM(Sheet1[acquisitions ]),FILTER(ALL(Sheet1),Sheet1[date]<=MAX(Sheet1[date])))

end of month pace =
VAR dayofmonth =
    DAY ( MAX ( Sheet1[date] ) )
VAR sub =
    MAX ( 'calendar'[days] ) - dayofmonth
VAR div = [cumulative acquisitions] / dayofmonth
RETURN
    div * sub
        + [cumulative acquisitions]

Best Regards

Maggie

[Anonymous]

Hi

I have read through the above and was trying to adapt it to my needs without success.

I have a table with all month end dates in and I am trying to add the unitprice for  that are active as at the month end date.

I have got the following but it will only work if you have one date in the table :

t_monthendtotals = the table with the dates in in whch I am adding the new column with this DAX

Amount= CALCULATE(Sum(billings[unitprice_base]),

FILTER(All(billings),AND(billings[createdon] <= t_monthendtotals[End Of Month Date],

OR(billings[TerminationDate] > t_monthendtotals[End Of Month Date],billings[TerminationDate] = BLANK())))

I was wondering if you could help me get the answer I need if at all it is possible in DAX .

Any help would be appreciated

Thank you

[ninakarsa]

I also tried to use this but it wouldnt work