How to obtain Month range from a date column along with cumulative total

[joshig]

I have the following dataset with date , amount and another column Spending. The Spending column can potentially have two values Expenditures and Non Expenditures. And I need to obtain cumulative/Running total based on the amount column corresponding to Date column that should display in the month range.

so i need something like below in my table/tablix visualizer from the data above

I have already  wrote measures for Expenditures and NonExpenditures

Expenditures= CALCULATE( SUM(AllExpendituresDataFormatted[AMOUNT]),AllExpendituresDataFormatted[SPENDING] = "Expenditure") and is able to represent data 

Date	Expenditures	NonExpenditures
June 2021	12121212	3232323
July 2021	23232323	23323

any help/suggestions/directions would be greatly appreciated.

[v-easonf-msft · Accepted solution]

Hi, @joshig 

You need enter the following data table which can also be easily created in excel.

Then try formulas as below:

cumulative amount_Expenditures = 
CALCULATE (
    AllExpendituresDataFormatted[Expenditures],
    FILTER (
        AllExpendituresDataFormatted,
        AllExpendituresDataFormatted[Date] >= SELECTEDVALUE ( 'Table'[Start Date] )
            && AllExpendituresDataFormatted[Date] <= SELECTEDVALUE ( 'Table'[End Date] )
    )
)

cumulative amount_NonExpenditures = 
CALCULATE (
    AllExpendituresDataFormatted[NonExpenditures],
    FILTER (
        AllExpendituresDataFormatted,
        AllExpendituresDataFormatted[Date] >= SELECTEDVALUE ( 'Table'[Start Date] )
            && AllExpendituresDataFormatted[Date] <= SELECTEDVALUE ( 'Table'[End Date] )
    )
)

sample result:

Best Regards,
Community Support Team _ Eason

[v-easonf-msft · Accepted solution]

Hi, @joshig 

Can you tell me if your problem is solved?

If you still need help, please share more information about it.

Best Regards,
Community Support Team _ Eason

[v-easonf-msft · Accepted solution]

Hi, @joshig 

You need enter the following data table which can also be easily created in excel.

Then try formulas as below:

cumulative amount_Expenditures = 
CALCULATE (
    AllExpendituresDataFormatted[Expenditures],
    FILTER (
        AllExpendituresDataFormatted,
        AllExpendituresDataFormatted[Date] >= SELECTEDVALUE ( 'Table'[Start Date] )
            && AllExpendituresDataFormatted[Date] <= SELECTEDVALUE ( 'Table'[End Date] )
    )
)

cumulative amount_NonExpenditures = 
CALCULATE (
    AllExpendituresDataFormatted[NonExpenditures],
    FILTER (
        AllExpendituresDataFormatted,
        AllExpendituresDataFormatted[Date] >= SELECTEDVALUE ( 'Table'[Start Date] )
            && AllExpendituresDataFormatted[Date] <= SELECTEDVALUE ( 'Table'[End Date] )
    )
)

sample result:

Best Regards,
Community Support Team _ Eason

[joshig]

This is awesome!! Kudos..

[amitchandak]

@joshig , to me it seems like YTD with year start in July

Assuming you have measure Expenditures, NonExpenditures with help from date table 

YTD Expenditures= CALCULATE([Expenditures],DATESYTD('Date'[Date],"6/30"))

YTD NonExpenditures = CALCULATE([NonExpenditures ],DATESYTD('Date'[Date],"6/30"))

[joshig]

Thanks Amit!!  but the issue is how do we get a column that have months as cumulative months and then apply the cumulative amount.

I tried as you suggested but its not rolling up. Am i missing something?