previous week calculation repeating over empty dates weekly to daily relationship
I have the below data model. Where the fact table is weekly and calendar is daily. When I calculate the previous week value it adds all the dates from the calendar rathen than keeping only dates in the fact table. PREVIOUSWEEK Value WDT = VAR _Today = SELECTEDVALUE(Lookup_Calendar[Date]) VAR _WOT = WEEKNUM( _Today, 1 ) -- Number 2 determines that the week begins on Monday. VAR _LW = _WOT - 1 RETURN CALCULATE( SUM( Data[Units]), FILTER( ALLSELECTED( Lookup_Calendar ), Lookup_Calendar[Week Of Year] = _LW && Lookup_Calendar[Year] = YEAR(_Today ) ) ) pbixfile
Help Needed! Week over Week revenue calculation "group by" categorical data
I am trying to figure out week over week calculation. I searched online and found DAX measure. I created columns for Year, WeekDay, and WeekNum. what I am using is like below: WoW Rev OTB = SUM ( 'Table'[sales] ) - CALCULATE ( SUM ( 'Table'[sales] ), FILTER ( ALL ( 'table' ), 'table'[Year] = MAX ( 'table'[Year] ) && 'table'[WeekNumber] = MAX ('table'[WeekNumber]) - 1 && 'table'[WeekDay] = MAX ( 'table'[WeekDay] ) ) ) The result turned very well, I got the total number for week over week revenue by different as of dates. It looks like the picture below: However, when I clicked slicers with categorical data, trying to see week over week revenue by filters, all the numbers showed negative: Eventually I wanted to present week over week in a column chart by As of Date. I have tried multiple measures from Power BI Community, but all the measures couldn't work when values apply on categorical data. I'm wondering if the measure should calculate by row, but mine is by column. Does anyone be able to help me with the week over week formula? Thank you in advance! Here is the pbix file without sensitive data: test-laughing chicken.pbixWeek over Week sales calculation
I am trying to figure out week over week calculation. I searched online and found DAX measure. I created columns for Year, WeekDay, and WeekNum. what I am using is like below: WoW Rev OTB = SUM ( 'Table'[sales on the book] ) - CALCULATE ( SUM ( 'Table'[sales on the book] ), FILTER ( ALL ( 'table' ), 'table'[Year] = MAX ( 'table'[Year] ) && 'table'[WeekNumber] = MAX ('table'[WeekNumber]) - 1 && 'table'[WeekDay] = MAX ( 'table'[WeekDay] ) ) ) The result turned very well, I got the total number for week over week sales. However, when I tried to apply on different region or destinations or choose other status, the week over week sales numbers showed all negative numbers which doesn't seem correct. (This is the screenshot for WoW sales without any filters) (After I selected "Established" in status slicer, all the numbers turn into negative. Same thing would happen if I selected product market slicer.) Can anyone help me how to apply week over week sales on categories? Thank you in advance!
