topic Help Needed! Week over Week revenue calculation "group by" categorical data in DAX Commands and Tips

Help Needed! Week over Week revenue calculation "group by" categorical data

Anonymous — Fri, 05 Aug 2022 16:20:49 GMT

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

Re: Help Needed! Week over Week revenue calculation "group by" categorical data

daXtreme — Fri, 05 Aug 2022 18:26:14 GMT

Anonymous

Such things are common in one-table models. Calculations in such models very often can't be made right however hard you try. Please create a good star-schema model first.

Re: Help Needed! Week over Week revenue calculation "group by" categorical data

Anonymous — Fri, 05 Aug 2022 20:06:33 GMT

Thank you for your reply!

Unfortunately, we most frequently work with one-table models, but mostly connect with additional Date table. Do you have any suggestions about building model to make better calculations? Thank you for your help!