Forum Discussion

kmes912's avatar
kmes912
Icon for Helper I rankHelper I
5 years ago

Previous Month Works for Certain Rows, But Some are Blank, Total is Correct

When looking at the summary matrix, the values are different than when I show the department matrix showing details.

 

Visual 1 shows correct and totals are correct at -598, but the details of the depts is not correct. The slicer is March to get this view which woudl be of February.

Fact Table 1 Showing P&L

Fact Table 2 Showing ATLAS

 

They are joined to a DEPT table, a DATE table and a UNIT table dimensionally. All are the same types/formats.

 

When I try to do PREVIOUSMONTH, it works for some, but not 0431, which is why the issue is coming up re the total vs the dept details. The below matrix has no filter on it except the unit. How does it have 852, 614, 545 but can't give it for prior P&L?

 

 

I've tried both DATEADD and PREVIOUSMONTH and am still getting blanks for 0431 despite the fact that i can see the values in the "P&L column"

Contract Hours P&L Prior = CALCULATE([Contract Hours P&L],DATEADD('01 Lookup Date'[Date],-1,MONTH))
Contract Hours ATLAS Prior = CALCULATE([Contract Hours ATLAS],PREVIOUSMONTH('01 Lookup Date'[Date]))
Contract Hours P&L = CALCULATE(SUMX('Table 09','Table 09'[Value]),'Table 09'[Worker Type]="Contract")
Contract Hours ATLAS = CALCULATE(SUM('03 Table'[Regular Hours]),'03 Table'[Class]="Contract")

3 Replies

    • kmes912's avatar
      kmes912
      Icon for Helper I rankHelper I
      5 years ago

      amitchandak Yes it is marked as a date table. So bizarre that it shows for the other departments but not this one. I verified that 0431 exists on both the fact and dimensions tables.  I dont get how it can pull current month fine but when you refer to prior works for certain depts but not for all.