Latest Enterprise DNA Initiatives

Week over Week Percentage %

DAX Calculations
#1

Hi, Guys, Could you please help me with this one? I need to calculate the Week over week sales change in percentage. Sales for current weekday is blank. How we can calculate the total percentage changes without including sales data from previous week if the current weekday sales is blank?

image

For example, I’d like to show the total sales percentage change as 50% instead of 6.93WoW_change%.pbix (25.4 KB) %.

Thanks!

Mike

#2

Hi @Mike,

You have a single flat table in your sample PBIX, no proper data model.
Just want to note that although this will work, it’s certainly not a best practice.

WoW Ch % = 
VAR vTable = CALCULATETABLE( Sheet1, Sheet1[This Week Sales] <> BLANK() )
RETURN

DIVIDE( 
    SUMX( vTable, [This Week Sales] ), 
    SUMX( vTable, [Last week Sales] )
)

or

WoW Ch % =
VAR Lw = CALCULATE( SUM( Sheet1[Last week Sales] ), Sheet1[This Week Sales] <> BLANK() )
VAR Tw = CALCULATE( SUM( Sheet1[This Week Sales] ), Sheet1[This Week Sales] <> BLANK() )
RETURN

DIVIDE( Tw, Lw )

with this result
image

1 Like
#3

@Melissa, Hi Melissa, you are absolutely right. The sample file you shared did not reflect what I need. I have update with the new sample file, cold you please take a look?

Thanks!

Mike

WoW_change%.pbix (59.7 KB)

#4

Hi @Mike,

Based on how you wrote your measures I assumed the ‘Date’[LastFiscalWeekX] is dynamic. If that is not the case check out the M code in this topic on how to create offsets in your Date table.

WoW Ch % = 
VAR vTable = FILTER( SUMMARIZE('Date', 'Date'[Weekday], 'Date'[LastFiscalWeekX] ),'Date'[LastFiscalWeekX] IN {0, 1} )
VAR nTable = ADDCOLUMNS( vTable, "@LW", [Last Week Sales], "@TW", [This Week Sales] )
RETURN

DIVIDE(
    SUMX( FILTER( nTable, 'Date'[LastFiscalWeekX] = 0), [@TW] ),
    SUMX( FILTER( nTable, 'Date'[LastFiscalWeekX] = 1 && [@TW] <> BLANK()), [@LW] )
)

with this result
image

1 Like
#5

@Melissa That is awesome, it is exactly what I was looking for. Thank you very very much!

Mike

#6

Hi @Mike, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

1 Like