Correct Table totals with a cumulative trial balance

[Kyle-92]

Hi Everyone!

I am busy building a dashboard where I need to create a table which shows monthly values for the income statement portion of a trial balance. The trouble is that the monthly trial balance's I receive have the cumulative values for the year (i.e January has only its revenue but then February has its revenue figures as well as Januarys)

I have been able to calculate the monthly values using dax but because the total value of a Powerbi table doesnt take into account row context, the total value for the monthly data does not equal all the rows added together. 

I have attached dummy data and a sample BI file (column 3 in the table therein is the monthly column where the total does not agree to the sum of all the rows)

 https://1drv.ms/u/s!AqSC3CpjQTrTvi60jhQIFNgJln-q?e=b8Ul2K 

[Ashish_Mathur]

Hi,

Try these measures:

Total in previous month = CALCULATE([Total],PREVIOUSMONTH(DateTable[Dates]))

Measure = if([Total]=CALCULATE([Total],DATESYTD(DateTable[Dates],"31/12")),[Total],[Total]-[Total in previous month])

Hope this helps.

[v-yangliu-msft · Accepted solution]

Hello @Kyle-92 ,

Here are the steps you can follow:

1. Create measure.

new_monthly_amount =
var _new=SUMMARIZE('DateTable','DateTable'[Dates],"value",[Monthly_Amount])
return IF(HASONEVALUE(DateTable[Dates]),[Monthly_Amount],SUMX(_new,[value]))

2. Result.

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[amitchandak]

@Kyle-92 , have you tried YTD with help from date table

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

overall cumm

Cumm Sales = CALCULATE(SUM(Table[value]),filter(allselected(Table),Table[Date] <=max(Table[Date])))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Kyle-92]

Hi @amitchandak, unfortunately this doesn't help as the trial balance amounts (the source data) are cumulative already so to explain via an example:

January sales =200

February sales =300

the source data would show January as 200 and February as 500.

Your YTD solution seems to keep balances cumulative so I can't show the table on a monthly basis. Unless I am doing this wrong?

You should see this in the data I attached with the power bi file.

basically the measures need to do the following:

1)take the revenue balance in 1 month and subtract the previous month (if the previous month is not in the year before) 

2)be able to have the correct total in a power Bi table visual

I have already done step 1 in my example file

Really appreciate your help!