Forum Discussion

thorpyuk's avatar
thorpyuk
Icon for Helper III rankHelper III
6 years ago
Solved

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 ...
Need Help
  • thorpyuk's avatar
    thorpyuk
    6 years ago

    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 

thorpyuk's avatar
thorpyuk
Icon for Helper III rankHelper III
6 years ago

 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's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
    thorpyuk
    Icon for Helper III rankHelper III
    6 years ago

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi thorpyuk ,

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

      Best Regards

      Rena

      • thorpyuk's avatar
        thorpyuk
        Icon for Helper III rankHelper III
        6 years ago

        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