sum of amounts from start date to selected date

[techvidya]

Hey guys,

Hope you are well and safe in this pandemic. Below is my requirment , for which iam expecting a expert solution.

I have Table A which has Project start date column and Table B which holds the Revenue for Each month(Month & Revenue columns)

Now, if i select any month in slicer(calender table), i should get the revenue from project start date to selected month in slicer.

For ex: if Project start date is April 2020 and i select june in slicer, i should get sum of Revenue from April,may,june.

if April Month Revenue is 5000

May is 2000

June is 3000

So on selecting june month in slicer, i should get revenue as 10000,

if i select may in slicer, i should get revenue as 7000

TIA

[v-rzhou-msft]

Hi @techvidya 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[v-rzhou-msft · Accepted solution]

Hi @techvidya 

Due to I don't know your data model, I build TableA and TableB to have a test.

TableA add Year column and Month column by calculated column.

Year = YEAR('Table A'[Project start date])

Month = MONTH('Table A'[Project start date])

TableB:

Build a Calendar Table.

Calendar = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]))

Here I build a Measure to get the sum of revenues between start month and select month in the same year as start date.

Measure =
VAR _selected =
    SELECTEDVALUE ( 'Calendar'[Month] )
RETURN
    SUMX (
        FILTER (
            'Table B',
            'Table B'[Month] <= _selected
                && 'Table B'[Year] = MAX ( 'Table A'[Year] )
                && 'Table B'[Month] >= MAX ( 'Table A'[Month] )
        ),
        'Table B'[Revenue]
    )

Result is as below.

Default:

Select May in Slicer, Project A will show Sum of Revenue from 2020/4 to 2020/5,C will show Sum of Revenue from 2019/4 to 2019/5.

You can download the pbix file from this link: sum of amounts from start date to selected date

If this reply still couldn't help you solve your problem, please show me the data model about TableA and TableB.

OR you can share your pbix file with me by your Onedrive for Business.

Best Regards,

Rico Zhou

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

[amitchandak]

@techvidya , do you have revenue before project start date if not this will work

measure =
var _max = maxx(allselected(Date),Date[Date])
return
calculate(Sum(TableB[revenue]), filter(TableB, TableB[Date] <_max ))

Assume you have date in table B

or

measure =
var _max = maxx(allselected(Date),Date[Date])
return
calculate(Sum(TableB[revenue]),Values(TableA[Project]),filter(TableB, TableB[Date] <_max && TableA >=max(projectA[startDate])))

measure =
var _max = maxx(allselected(Date),Date[Date])
return
calculate(SumX(Values(TableA[Project]), TableB[revenue]), filter(TableB, TableB[Date] <_max && TableA >=max(projectA[startDate])))

[techvidya]

@amitchandak , can you pls be more precise on this

measure =
var _max = maxx(allselected(Date),Date[Date])
return
calculate(Sum(TableB[revenue]),Values(TableA[Project]),filter(TableB, TableB[Date] <_max && TableA >=max(projectA[startDate])))

[amitchandak]

@techvidya , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[techvidya]

@amitchandak ,

I will have the revenue before project date also, but it will be for another year.