Cumulative value measure in description wise (Sales,Expenses) with filters for product

[klllmmm-buks]

I'm trying to create a line chart that shows the cumulative sales and expenses for my two products.

I have sales and expenses data in two Excel tables. I also created cumulative amounts in each of those tables as I was unable to do it in Power BI.

I loaded the Sales and Expense tables into Power BI and then appended the two tables.

Next, I created two measures for Cumulative Sales and Cumulative Expenses using the following formulas:

Cumulative Expenses = 
CALCULATE(
    SUM('Append1'[Cumulative]),
    'Append1'[Description] = "Expenses"
)

Then, I created a line chart using the cumulative values from the above two measures.

However, the values shown in the line chart do not appear to be correct as cumulative values can not be decreased than thevalue before as there are no negative values.

How can i correct this graph to get correct Visual.
https://github.com/KelumPerera/Collection/blob/Test/PBI_ProductWise_Cumulative_ValueVisual.pbix 

[anmolmalviya05]

Hi @klllmmm-buks, Please try to create below measure:

Cumulative Sales =
CALCULATE(
SUM('Append1'[Amount]),
FILTER(
ALLSELECTED('Append1'),
'Append1'[Date] <= MAX('Append1'[Date]) &&
'Append1'[Description] = "Sales"
)
)

Cumulative Expenses =
CALCULATE(
SUM('Append1'[Amount]),
FILTER(
ALLSELECTED('Append1'),
'Append1'[Date] <= MAX('Append1'[Date]) &&
'Append1'[Description] = "Expenses"
)
)

[danextian]

Hi @klllmmm-buks 

You need to make use of dimension tables to be able to graph your sales and expenses into one visual.

Create a calendar and products tables in DAX or M (sample DAX formula in the pbix), relate the columns from these dim tables to your fact tables, apply calculations on these columns and use these columns in the viz (not the ones from either sales or expenses.

CumulativeExpenses = 
CALCULATE (
    SUM ( Expenses[Amount] ),
    FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
)

CumulativeSales = 
CALCULATE (
    SUM ( Sales[Amount] ),
    FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
)

Note: While combining these two tables might be a good idea, it will lengthen the refresh time on large tables.