Skip to main content
cancel
Showing results for 
Search instead 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

Reply
laughingchicken
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:
laughingchicken_0-1659715583596.png
However, when I clicked slicers with categorical data, trying to see week over week revenue by filters, all the numbers showed negative:
laughingchicken_2-1659715661315.png

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
daXtreme
Solution Sage
Solution Sage

@laughingchicken 

 

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.

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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors