Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
thorpyuk
Helper III
Helper III

Change Cumulative values to daily

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
1 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 

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

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

 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):

Temp.jpg

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])))

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])))

 

Hi @thorpyuk ,

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

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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