Forum Discussion
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.
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
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!
11 Replies
Have you tried the quick measures? You can filter it using the quick measure
Sorry, my response is not suitable to your need. Apologies.
Have you tried to remove ALL from the code?
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
Check this file
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 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.
