Forum Discussion

arshad11's avatar
arshad11
Frequent Visitor
5 years ago

MTD CALCULATION

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 NoJob DateEstimated RevenueEstimated CostEstimated Profit
LE-2020/06/00203/Jun/2020                              3,259                    2,020                       1,239
AE-2020/06/00106/Jun/2020                            30,438                  28,063                       2,375
AE-2020/06/00307/Jun/2020                                  645                        395                          250
AE-2020/06/00207/Jun/2020                            23,127                  21,656                       1,471
AI-2020/06/00107/Jun/2020                              1,473                    1,207                          266
AI-2020/06/00207/Jun/2020                              1,909                    1,581                          328
AI-2020/06/00307/Jun/2020                              1,785                    1,419                          366
AI-2020/06/00407/Jun/2020                              1,785                    1,419                          366
AI-2020/06/00514/Jun/2020                              1,411                    1,122                          289
AI-2020/06/00616/Jun/2020                              1,868                    1,485                          383
AI-2020/06/00716/Jun/2020                              1,632                    1,088                          544
AI-2020/06/00817/Jun/2020                              1,992                    1,632                          360
AI-2020/06/00901/Jul/2020                              1,992                    1,632                          360
AI-2020/06/01002/Jul/2020                              1,536                    1,088                          448
AI-2020/06/01105/Jul/2020                                  460                        210                          250
FE-2020/06/00105/Jul/2020                          116,640                115,913                          727
FE-2020/06/00207/Jul/2020                              9,473                    8,298                       1,175
AE-2020/06/00408/Jul/2020                            18,085                  16,995                       1,090
FI-2020/06/00208/Jul/2020                            34,071                  32,081                       1,990

 

 

amitchandak parry2k Ashish_Mathur 

 

6 Replies