Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Previous Week values (not summarized)

  Hi! Need a help with this case please.  I'm trying to get previous week data in the last column, and actually i got it, but it shows the summ of all GECs, and i need to split it GEC by GEC. ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Anonymous 

     

    Use this DAX. Before using just shift your measure under another table 

    Modeling  - Properties - Home Table  - select other table than TTL_employees

     

     

    test PrevWeek Bill HC = 
CALCULATE (
    SUM ( Table1[Billable] ),
        FILTER (
            ALLEXCEPT( Table1,Table1[GEC] ),
            Table1[Week1] = SELECTEDVALUE ( Table1[Week1] ) -1 &&
            YEAR ( Table1[Date] ) = SELECTEDVALUE ( Table1[Year] )
        )
)


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    Proud to be Datanaut!

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Anonymous v-xuding-msft Thank you all for the help, looks like I found what was wrong, but don't know how to fix it. 
My data table [TTL_Employees] looks next >


I have a SiteKey in there, that connected to anothe Lookup table - Site_Lookup >

When my mesure is

test PrevWeek Bill HC = 
CALCULATE (
    SUM ( TTL_Employees[Billable] ),
        FILTER (
            ALLEXCEPT ( TTL_Employees, TTL_Employees[SiteKey] ),
            TTL_Employees[WeekNo] = SELECTEDVALUE ( TTL_Employees[WeekNo] ) -1 &&
            YEAR ( TTL_Employees[Date] ) = SELECTEDVALUE ( TTL_Employees[Year] )
        )
)

and Columns in the table are Date, SiteKey (from the table TTL_Employees), test PrevWeek Bill HC - it works fine >

but when I'm trying to replace SiteKey (from the table TTL_Employees) with a SiteKey from the table Site_Lookup - it shows only totals >


Please advice whats wrong here. I'm quite new with Power BI and relationships a bit difficult to understand in such cases.