Problem in calculating Current Month and Previous Month for all Cost Centers

[mai_daftari]

Hello ,

Hope you are doing well.

I am facing a problem is calculating “Current Month Sales” and “Previous Month Sales” for all Cost Centers (Departments), measures are calculating correctly for only one cost center and not for others.

Here is the view of matrix where you can easily see that the column “Sales” and “Last month sales” are being calculated correctly for Cost Center "Transport" but not for Cost Center “Trading”, however “revenue_sum” column for both cost centers shows sales correctly.

I am using following measure for calculating “Revenue_Sum”, “Total monthly Sales” and “Previous month sales”

Revenue_Sum

Revenue_Sum = Sum(VU_GET_ALL_REVENUES[Amount])

Sales

Current_Sales_MTD = CALCULATE(sum(VU_GET_ALL_REVENUES[Amount]), DATESMTD('Calendar'[DATE]))

Last Month Sales

Previous_Sales_MTD = CALCULATE(SUM(VU_GET_ALL_REVENUES[Amount]) ,  PREVIOUSMONTH(DATESMTD('Calendar'[DATE]))

I have also attached relationships image

With the following details

• VU_GET_ALL_COST_CENTER_GROUPS[cost_center_id]    1 : *   VU_GET_ALL_COST_CENTERS[Group_id]
• VU_GET_ALL_COST_CENTERS[Cost_center_Id]   1:*   VU_GET_ALL_REVENUES[Cost_Center_Id]
• Calender[Date]   1:*  VU_GET_ALL_REVENUES[Date_]

kindly assist where I am making a mistake that’s stopping it from calculating “Current Month” and “Previous Month Sales” for other cost centers  and how to resolve this issue.

I also tried replacing PREVIOUSMONTH and MTD with DateADD in above measures, but did not work

[MattAllington · Accepted solution]

Is there a reason you're using MTD?  Given you have many months in your visual, the months are completed and hence you may not need MTD. So unless this is to solve some specific reporting issue, I suggest you simplify as follows

Current_Sales = sum(VU_GET_ALL_REVENUES[Amount])
 
Previous_Sales_MTD = CALCULATE(SUM(VU_GET_ALL_REVENUES[Amount]),                              PREVIOUSMONTH('Calendar'[DATE]))

if that doesn't solve the problem, I suggest you creat a new table on a new sheet

add. A. Slicer for cost centre and select the cost centre at issue

place year and month from the calendar table on rows

drag the raw column VU_GET_ALL_REVENUES[Amount] and put it on values. Does it work?  If not, it's not the DAX that is the problem, it's the data. 
add the 2 measures and see if you can spot any issues. 

[mai_daftari]

Thanks MattAllington for the reply.

I just did as said by you, removed MTD and used PREVIOUSMONTH, 

Current_Sales = sum(VU_GET_ALL_REVENUES[Amount])

Previous_Sales = CALCULATE(SUM(VU_GET_ALL_REVENUES[Amount]) ,  PREVIOUSMONTH(('Calendar'[DATE]))

Now i am able to see current sales but not previous month sales

[MattAllington]

You should have year and month in your visual, not just month.

[mai_daftari]

Hi @MattAllington ,

I even tried that, the result is the same, showing previous month sales of Cost Center "Transport", other cost centers are not displaying previous month sales.

Now just for second opinion, what could be wrong with the data that may cause such behaviour 

[MattAllington]

My start by assumption is there is no matching data. When I face this problem, add the cost centre number from the dim table and also from the fact table into the visual. Probably better to swap it for a table, not a matrix. My guess is there's no data in the fact table that matches the code in the sim table.  

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[mai_daftari]

Hi Ashish,

Thanks for the reply. while preparing pbix file (removing all other tables except revenue and date and cost centers), the visual started showing results.

It looks like a relationship problem, thanks aneways, image attached.

[DataNinja777]

Hi @mai_daftari ,

Out of curiosity, I always thought cost centers were typically used for operating expenses, not for revenues, which are usually assigned to profit centers.

That aside, I attempted to replicate the issue you're describing using some dummy data with blank months, but I couldn't reproduce the problem.

Thank you, MattAllington , for your advice.

Previous_Sales_MTD = CALCULATE( SUM(VU_GET_ALL_REVENUES[Amount]), DATESMTD(PARALLELPERIOD('Calendar'[DATE], -1, MONTH)) )

Using random data, I tried to recreate the issue you’re experiencing, but I wasn't able to, except for the missing total row for the previous month, which is shown in the second table. The first table uses the modified formula in the code snippet.

Best regards,

[mai_daftari]

Hi @DataNinja777  , thankyou very much for your effors for re-producing the error. I my self confused as to what is wrong with the data that measures are failing to calculate.