MTD CALCULATION

[arshad11]

Hi,

I am new to Power BI and having trouble in understanding how are MTD calculations are done on Power BI allthough I have tried at looking at multiple videos and articles. 

I have a table, that has a list of Job IDs and the expected revenue and profit. All I have to do is compare the MTD revenue and profit between the months and I want the data to get automatically get updated once the data set is updated. For example, MTD June data to be compared with MTD July, MTD July data compared to MTD August. 

- Referring online solutions, I have created a date table (Dim Date) and marked it as a Date table. 

 - I hasve created a column as well - MTD Profit = TOTALMTD(SUM(Sheet1[Estimated Revenue),'Dim Date'[Date])

- created a measure MTD Revenue = TOTALMTD(SUM(Sheet1[Estimated Revenue]),'Dim Date'[Date])

- After creating a table to in Power BI, the total sum is being calculated for every date and it shows that instead of showing rolling sum. For example, the total estimated revenue for 07/06/2020  is shown as USD 30,724. The estimated values for 03/06/2020 and 06/06/2020 were not added to it. I am not sure where I am going wrong. Require help please! Also, could you kind recommend the most apt chart to show this as well

Job No	Job Date	Estimated Revenue	Estimated Cost	Estimated Profit
LE-2020/06/002	03/Jun/2020	3,259	2,020	1,239
AE-2020/06/001	06/Jun/2020	30,438	28,063	2,375
AE-2020/06/003	07/Jun/2020	645	395	250
AE-2020/06/002	07/Jun/2020	23,127	21,656	1,471
AI-2020/06/001	07/Jun/2020	1,473	1,207	266
AI-2020/06/002	07/Jun/2020	1,909	1,581	328
AI-2020/06/003	07/Jun/2020	1,785	1,419	366
AI-2020/06/004	07/Jun/2020	1,785	1,419	366
AI-2020/06/005	14/Jun/2020	1,411	1,122	289
AI-2020/06/006	16/Jun/2020	1,868	1,485	383
AI-2020/06/007	16/Jun/2020	1,632	1,088	544
AI-2020/06/008	17/Jun/2020	1,992	1,632	360
AI-2020/06/009	01/Jul/2020	1,992	1,632	360
AI-2020/06/010	02/Jul/2020	1,536	1,088	448
AI-2020/06/011	05/Jul/2020	460	210	250
FE-2020/06/001	05/Jul/2020	116,640	115,913	727
FE-2020/06/002	07/Jul/2020	9,473	8,298	1,175
AE-2020/06/004	08/Jul/2020	18,085	16,995	1,090
FI-2020/06/002	08/Jul/2020	34,071	32,081	1,990

@amitchandak @parry2k @Ashish_Mathur 

[Greg_Deckler]

@arshad11 - I'm with @dedelman_clng , needs to be a measure. Did you get this resolved?

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[arshad11]

@Greg_Deckler @dedelman_clng I have tried using MTS Revenue as a measure but Im still not able to get the end result

[Ashish_Mathur]

Hi,

On the Data that you have shared in your original post, show the exact result that you are expecting.

[arshad11]

@Ashish_Mathur  

Hi, i have tried to draw what Im expecting to do get. Hope it helps!

[Ashish_Mathur]

Hi,

I still do not understand.  If on the x axis, you have date of August, then why should you have a line for July MTD?

[dedelman_clng]

Hi @arshad11 - MTD Revenue needs to be a measure, not a calculated column. It will "become" a column when you add it to a table visual.

Hope this helps

David