Previous Week values (not summarized)

[Anonymous]

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.

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

Will be very appreciate for the help, because I stucked on this for few days so far. 

[Anonymous]

@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.

[Anonymous · Accepted solution]

@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]

@Anonymous thnks, but have the same result as I had befor in my first message 

[Anonymous]

@Anonymous 

Check this file

https://www.dropbox.com/s/b909t1l5tyw85uc/Previous%20Week%20values%20%28not%20summarized%29.pbix?dl=0

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]

@Anonymous  I see. In  your example you have different dates for each row, in mine - I have different data for each GEC in the same day. Thats why your file works perfect, but it doesn't fit to my case. 

[Anonymous]

@Anonymous 

If you can try Power Query solution then use below link

https://www.dropbox.com/s/b909t1l5tyw85uc/Previous%20Week%20values%20%28not%20summarized%29.pbix?dl=0

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!

[v-xuding-msft]

Hi @Anonymous ,

Can you please attach a dummy file? Based on my test, the measure worked when I changed ALL to ALLEXCEPT.

Measure = 
CALCULATE (
    SUM ( Table1[Billable] ),
        FILTER (
            ALLEXCEPT( Table1,Table1[GEC] ),
            Table1[Week] = SELECTEDVALUE ( Table1[Week] ) -1 &&
            YEAR ( Table1[Date] ) = SELECTEDVALUE ( Table1[Year] )
        )
)

Best Regards,

Xue

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

[mussaenda]

Have you tried the quick measures? You can filter it using the quick measure

[Anonymous]

no, I haven't. you mean to replace FILTER () part with quick mesure filter?

[mussaenda]

Sorry, my response is not suitable to your need. Apologies.

Have you tried to remove ALL from the code?

[Anonymous]

yes. and I got the empty last column 😞