Forum Discussion

munnaz's avatar
munnaz
Frequent Visitor
5 years ago
Solved

cumulative value over time

Hi All,

 

I have problem trying to figure this out. What is the way to tackle this problem...

Many thanks in advance

 

I have a set of data given for two sites as below.

SiteDateSalesCost
Brisbane7/31/201798726396
Toowoomba7/31/2017773469
Brisbane8/31/2017121407818
Toowoomba8/31/20171139739
Brisbane10/31/2017133628619
Toowoomba10/31/20171341851
Brisbane11/30/2017145049326
Toowoomba11/30/20171531982
Brisbane12/31/20171558310074
Toowoomba12/31/201716491091
Brisbane1/31/20181336846
Toowoomba1/31/201815794
Brisbane2/28/201825181632
Toowoomba2/28/2018292172
Brisbane3/31/201839052598
Toowoomba3/31/2018450264
Brisbane4/30/201852133481
Toowoomba4/30/2018724414
Brisbane5/31/201864534312
Toowoomba5/31/2018959587
Brisbane6/30/201877995252
Toowoomba6/30/20181208722
Brisbane8/31/201897866584
Toowoomba8/31/201816641003

 

The sales and costs are given as cumulative starting from 01/01 of every year until the 12/31 of the year.

 

I would like to start a overall cumulative figure by giving both sites sales & costs 0 from the 7/31/17. Then add it difference of each month to this coloumn. Taking into account that values given are restarted at the beginning of the year.

 

I would like to see this result

SiteDateSalesCostMontly SalesMonthly CostCumalative Sales Since 7/31Cumalative Cost since 7/31
Brisbane7/31/2017987263960000
Toowoomba7/31/20177734690000
Brisbane8/31/20171214078182268142222681422
Toowoomba8/31/20171139739366270366270
Brisbane10/31/2017133628619122280134902223
Toowoomba10/31/20171341851202112568382
Brisbane11/30/2017145049326114270746322930
Toowoomba11/30/20171531982190131758513
Brisbane12/31/20171558310074107974857113678
Toowoomba12/31/201716491091118109876622
Brisbane1/31/20181336846133684670474524
Toowoomba1/31/201815794157941033716
Brisbane2/28/201825181632118278682295310
Toowoomba2/28/2018292172135781168794
Brisbane3/31/201839052598138796696166276
Toowoomba3/31/2018450264158921326886
Brisbane4/30/2018521334811308883109247159
Toowoomba4/30/201872441427415016001036
Brisbane5/31/2018645343121240831121647990
Toowoomba5/31/201895958723517318351209
Brisbane6/30/2018779952521346940135108930
Toowoomba6/30/2018120872224913520841344
Brisbane8/31/201897866584198713321549710262
Toowoomba8/31/201816641003

45628125401625

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    munnaz 

    The 4 dax formulas are not short, please see the attached pbix for detail.

    Monthly Sales ! = 
var Previous_date = CALCULATE(MAX('Table'[Date]),FILTER('Table',[Date]<EARLIER('Table'[Date])))
var previous_sales= CALCULATE(MIN('Table'[Sales]),FILTER('Table',[Date]=Previous_date && 'Table'[Site]=EARLIER('Table'[Site])))
Return IF([Date]=DATE(2017,7,31),0, IF([Date]=CALCULATE(MIN([Date]),ALLEXCEPT('Table','Table'[Date].[Year])),[Sales], IF([Sales]>previous_sales,[Sales]-previous_sales,previous_sales-[Sales])))

Montnly Cost ! = 
var Previous_date = CALCULATE(MAX('Table'[Date]),FILTER('Table',[Date]<EARLIER('Table'[Date])))
var previous_cost= CALCULATE(MIN('Table'[Cost]),FILTER('Table',[Date]=Previous_date && 'Table'[Site]=EARLIER('Table'[Site])))
Return IF([Date]=DATE(2017,7,31),0,IF([Date]=CALCULATE(MIN([Date]),ALLEXCEPT('Table','Table'[Date].[Year])),[Cost],IF([Cost]>previous_cost,[Cost]-previous_cost,previous_cost-[Cost])))

Cumulative Sales = CALCULATE(SUM([Monthly Sales !]),FILTER('Table',[Date]<=EARLIER([Date])&&[Site]=EARLIER('Table'[Site])))

Cumulative Cost = CALCULATE(SUM([Montnly Cost !]),FILTER('Table',[Date]<=EARLIER([Date])&&[Site]=EARLIER('Table'[Site])))

     

     

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    munnaz , Try with date table like

    //Year

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

    //over all

    Cumm Sales = CALCULATE(SUM(Table[Sales]),filter(date,date[date] <=max(date[Date])))

     

    Or like

    Cumm Sales = CALCULATE(SUM(Table[Sales]),filter(allselcted(Table),Table[date] <=max(Table[Date]) && Table[Site] =max(Table[Site]) ))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

    See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


    Appreciate your Kudos.

    • clubspec's avatar
      clubspec
      Icon for Helper III rankHelper III
      5 years ago

      Another DAX similar to amitchandak below would be:

      YTD Sales = TOTALYTD(SUM(Sales[Sales Amount]),yourdatetable[DateKey],,"12/31")

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    munnaz 

    The 4 dax formulas are not short, please see the attached pbix for detail.

    Monthly Sales ! = 
var Previous_date = CALCULATE(MAX('Table'[Date]),FILTER('Table',[Date]<EARLIER('Table'[Date])))
var previous_sales= CALCULATE(MIN('Table'[Sales]),FILTER('Table',[Date]=Previous_date && 'Table'[Site]=EARLIER('Table'[Site])))
Return IF([Date]=DATE(2017,7,31),0, IF([Date]=CALCULATE(MIN([Date]),ALLEXCEPT('Table','Table'[Date].[Year])),[Sales], IF([Sales]>previous_sales,[Sales]-previous_sales,previous_sales-[Sales])))

Montnly Cost ! = 
var Previous_date = CALCULATE(MAX('Table'[Date]),FILTER('Table',[Date]<EARLIER('Table'[Date])))
var previous_cost= CALCULATE(MIN('Table'[Cost]),FILTER('Table',[Date]=Previous_date && 'Table'[Site]=EARLIER('Table'[Site])))
Return IF([Date]=DATE(2017,7,31),0,IF([Date]=CALCULATE(MIN([Date]),ALLEXCEPT('Table','Table'[Date].[Year])),[Cost],IF([Cost]>previous_cost,[Cost]-previous_cost,previous_cost-[Cost])))

Cumulative Sales = CALCULATE(SUM([Monthly Sales !]),FILTER('Table',[Date]<=EARLIER([Date])&&[Site]=EARLIER('Table'[Site])))

Cumulative Cost = CALCULATE(SUM([Montnly Cost !]),FILTER('Table',[Date]<=EARLIER([Date])&&[Site]=EARLIER('Table'[Site])))

     

     

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