cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

2 REPLIES 2
Solution Sage

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.

Frequent Visitor

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.