cumulative value over time

[munnaz]

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.

Site	Date	Sales	Cost
Brisbane	7/31/2017	9872	6396
Toowoomba	7/31/2017	773	469
Brisbane	8/31/2017	12140	7818
Toowoomba	8/31/2017	1139	739
Brisbane	10/31/2017	13362	8619
Toowoomba	10/31/2017	1341	851
Brisbane	11/30/2017	14504	9326
Toowoomba	11/30/2017	1531	982
Brisbane	12/31/2017	15583	10074
Toowoomba	12/31/2017	1649	1091
Brisbane	1/31/2018	1336	846
Toowoomba	1/31/2018	157	94
Brisbane	2/28/2018	2518	1632
Toowoomba	2/28/2018	292	172
Brisbane	3/31/2018	3905	2598
Toowoomba	3/31/2018	450	264
Brisbane	4/30/2018	5213	3481
Toowoomba	4/30/2018	724	414
Brisbane	5/31/2018	6453	4312
Toowoomba	5/31/2018	959	587
Brisbane	6/30/2018	7799	5252
Toowoomba	6/30/2018	1208	722
Brisbane	8/31/2018	9786	6584
Toowoomba	8/31/2018	1664	1003

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

Site	Date	Sales	Cost	Montly Sales	Monthly Cost	Cumalative Sales Since 7/31	Cumalative Cost since 7/31
Brisbane	7/31/2017	9872	6396	0	0	0	0
Toowoomba	7/31/2017	773	469	0	0	0	0
Brisbane	8/31/2017	12140	7818	2268	1422	2268	1422
Toowoomba	8/31/2017	1139	739	366	270	366	270
Brisbane	10/31/2017	13362	8619	1222	801	3490	2223
Toowoomba	10/31/2017	1341	851	202	112	568	382
Brisbane	11/30/2017	14504	9326	1142	707	4632	2930
Toowoomba	11/30/2017	1531	982	190	131	758	513
Brisbane	12/31/2017	15583	10074	1079	748	5711	3678
Toowoomba	12/31/2017	1649	1091	118	109	876	622
Brisbane	1/31/2018	1336	846	1336	846	7047	4524
Toowoomba	1/31/2018	157	94	157	94	1033	716
Brisbane	2/28/2018	2518	1632	1182	786	8229	5310
Toowoomba	2/28/2018	292	172	135	78	1168	794
Brisbane	3/31/2018	3905	2598	1387	966	9616	6276
Toowoomba	3/31/2018	450	264	158	92	1326	886
Brisbane	4/30/2018	5213	3481	1308	883	10924	7159
Toowoomba	4/30/2018	724	414	274	150	1600	1036
Brisbane	5/31/2018	6453	4312	1240	831	12164	7990
Toowoomba	5/31/2018	959	587	235	173	1835	1209
Brisbane	6/30/2018	7799	5252	1346	940	13510	8930
Toowoomba	6/30/2018	1208	722	249	135	2084	1344
Brisbane	8/31/2018	9786	6584	1987	1332	15497	10262
Toowoomba	8/31/2018	1664	1003

45628125401625

[V-pazhen-msft · Accepted solution]

@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.

[amitchandak]

@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-Y...


Appreciate your Kudos.

[clubspec]

Another DAX similar to amitchandak below would be:

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