Change Cumulative values to daily

[thorpyuk]

Hi all,

I have a chart displaying daily sales values for the current week split out by day, and another measure for 'last week to date' shown below which will calculate the cumulative sales for the previous week so that i can do a week-on-week var. The issue is that when i drag this onto my daily chart, it shows cumulative for the previous week (makes sense i know!), but i want to show the previous week split out by its daily actuals (EG not cumulative) if possible? Here's my measure for the previous week to date:

NetSalesIncLWTD =

VAR CurrentWeek = SELECTEDVALUE( 'db_v_BaseCalendar_TYLY'[WeekNo] )

VAR CurrentYear = SELECTEDVALUE( 'db_v_BaseCalendar_TYLY'[FinYear] )

VAR CurrentDay = Max ('db_v_BaseCalendar_TYLY'[DayNo] )

RETURN

CALCULATE( [NetSalesIncM],

FILTER( ALL( 'db_v_BaseCalendar_TYLY' ),

        'db_v_BaseCalendar_TYLY'[WeekNo] = CurrentWeek -1 && 'db_v_BaseCalendar_TYLY'[FinYear] = CurrentYear && 'db_v_BaseCalendar_TYLY'[DayNo] <= CurrentDay ))

Thanks in advance

[amitchandak]

Week Rank and Week number will along with date calendar.

Check WTD page

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

[thorpyuk]

 Amitchandak, i already have a week to date and last week to date - i want to show them differently in my chart though.

In this picture i am gettin the top version (cumulative), but i want the bottom version to show (daily):

[amitchandak]

Try like

WTD = 
var _max = maxx(ALLSELECTED('Date'),'Date'[Weekday])
return
CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[Weekday]=max('Date'[Weekday])))

[thorpyuk]

Nearly! It's worked for the current week, but i want the previous week. Here's my edited version of your code:

LW = 
var _max = maxx(ALLSELECTED('db_v_BaseCalendar_TYLY'),'db_v_BaseCalendar_TYLY'[DateName])
return
CALCULATE(SUM(db_v_SalesProduct[NetSales]),FILTER(all('db_v_BaseCalendar_TYLY'),'db_v_BaseCalendar_TYLY'[WeekID]=max('db_v_BaseCalendar_TYLY'[WeekID]) && 'db_v_BaseCalendar_TYLY'[DateName]=max('db_v_BaseCalendar_TYLY'[DateName])))

[v-yiruan-msft]

Hi @thorpyuk ,

So if you have gotten the solution? Is there anything else can help you for this thread?

Best Regards

Rena

[thorpyuk · Accepted solution]

Hi Rena, yes in the end i edited Amitchandak's code slightly to go 1 week back:

NetSalesIncLW = 
var _max = maxx(ALLSELECTED('db_v_BaseCalendar_TYLY'),'db_v_BaseCalendar_TYLY'[DateName])
return
CALCULATE(SUMX(db_v_SalesProduct, [NetSalesIncM]),FILTER(all('db_v_BaseCalendar_TYLY'),'db_v_BaseCalendar_TYLY'[WeekNo]=max('db_v_BaseCalendar_TYLY'[WeekNo])-1 && 'db_v_BaseCalendar_TYLY'[DateName]=max('db_v_BaseCalendar_TYLY'[DateName])))

Thanks 

[v-yiruan-msft]

Hi @thorpyuk ,

Thanks for your reply. I'm glad to hear that your problem has been resolved. Could you please mark helpful post as "Answered"? It will help the other members in community easy to find it when they face the similar problem with you. Thank you.

Best Regards

Rena