cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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.

Screenshot_2.jpg

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. 

1 ACCEPTED SOLUTION
nsrshkh1
Resolver IV
Resolver IV

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

 

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

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

Screenshot_4.jpg


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

Screenshot_5.jpg

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 >

Screenshot_6.jpg

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 >

Screenshot_7.jpg


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

nsrshkh1
Resolver IV
Resolver IV

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

 

View solution in original post

Anonymous
Not applicable

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

@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
Not applicable

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

 

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!

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

4.PNG

Best Regards,

Xue

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

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mussaenda
Resident Rockstar
Resident Rockstar

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

Anonymous
Not applicable

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

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

 

Have you tried to remove ALL from the code?

Anonymous
Not applicable

yes. and I got the empty last column 😞

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.