End of Month Total Cost on Hold

[MorePowerBI]

I want to create a summary table or measure (whichever is appropriate) that shows the End of Month total cost on hold.

Here's the data table:

Hold Assigned	Hold Unassigned	Cost
1/1/2020	3/1/2020	$27,633.31
1/1/2020	2/1/2020	$3,539,210.76
1/1/2020	1/1/2020	$3,377,478.80
2/1/2020	5/1/2020	$162,544.44
2/1/2020	4/1/2020	$5,631.62
2/1/2020	3/1/2020	$387,690.03
2/1/2020	2/1/2020	$4,340,867.64
3/1/2020	7/1/2020	$319,570.85
3/1/2020	6/1/2020	$900,650.89
3/1/2020	5/1/2020	$867,777.66
3/1/2020	4/1/2020	$4,766,452.98
3/1/2020	3/1/2020	$9,953,468.13
4/1/2020	8/1/2020	$362,153.01
4/1/2020	7/1/2020	$384,938.27
4/1/2020	6/1/2020	$356,583.83
4/1/2020	5/1/2020	$1,890,412.76
4/1/2020	4/1/2020	$3,758,262.05
5/1/2020	9/1/2020	$92,215.48
5/1/2020	7/1/2020	$88,314.70
5/1/2020	6/1/2020	$4,856,607.76
5/1/2020	5/1/2020	$7,894,614.51
6/1/2020	9/1/2020	$256,110.61
6/1/2020	8/1/2020	$966,163.58
6/1/2020	7/1/2020	$3,373,959.95
6/1/2020	6/1/2020	$7,184,874.89
7/1/2020	12/1/2020	$328,905.39
7/1/2020	9/1/2020	$250,091.26
7/1/2020	8/1/2020	$2,389,127.12
7/1/2020	7/1/2020	$9,020,577.40
8/1/2020	12/1/2020	$662,568.93
8/1/2020	11/1/2020	$137,960.85
8/1/2020	9/1/2020	$379,822.07
8/1/2020	8/1/2020	$5,552,546.98
9/1/2020	12/1/2020	$645,260.02
9/1/2020	11/1/2020	$36,195.80
9/1/2020	10/1/2020	$1,838,781.34
9/1/2020	9/1/2020	$4,592,379.78
10/1/2020	12/1/2020	$377,350.39
10/1/2020	11/1/2020	$799,488.08
10/1/2020	10/1/2020	$6,383,927.56
11/1/2020	2/1/2021	$312,846.68
11/1/2020	1/1/2021	$1,067,142.22
11/1/2020	12/1/2020	$6,113,607.14
11/1/2020	11/1/2020	$5,207,123.22
12/1/2020	1/1/2021	$384,195.73
12/1/2020	12/1/2020	$2,810,326.36
1/1/2021	3/1/2021	$51,608.07
1/1/2021	2/1/2021	$937,482.94
1/1/2021	1/1/2021	$1,733,842.25
2/1/2021	3/1/2021	$1,002,841.07
2/1/2021	2/1/2021	$5,089,770.61
3/1/2021	4/1/2021	$154,740.15
3/1/2021	3/1/2021	$4,511,765.26
4/1/2021	5/1/2021	$552,035.22
4/1/2021	4/1/2021	$6,985,082.55
4/1/2021		$470,423.17

And this is the expected result:

End of Month	Total Cost
Jan-20	$3,566,844.07
Feb-20	$583,499.40
Mar-20	$7,022,628.44
Apr-20	$5,244,631.71
May-20	$7,361,034.79
Jun-20	$5,843,426.45
Jul-20	$4,644,766.45
Aug-20	$2,107,674.59
Sep-20	$3,649,672.33
Oct-20	$2,987,729.46
Nov-20	$9,507,680.77
Dec-20	$1,764,184.63
Jan-21	$1,301,937.69
Feb-21	$1,054,449.14
Mar-21	$154,740.15
Apr-21	$1,022,458.39

To get the resulting table, I'm taking the sum of the Cost column where Hold Assigned < Hold Unassigned at the end of each month. For example, the Total Cost on hold at the end of May 2020 is the sum of the Cost for the following records:

Hold Assigned	Hold Unassigned	Cost
3/1/2020	7/1/2020	$319,570.85
3/1/2020	6/1/2020	$900,650.89
4/1/2020	8/1/2020	$362,153.01
4/1/2020	7/1/2020	$384,938.27
4/1/2020	6/1/2020	$356,583.83
5/1/2020	9/1/2020	$92,215.48
5/1/2020	7/1/2020	$88,314.70
5/1/2020	6/1/2020	$4,856,607.76

[Ashish_Mathur]

Hi,

On the third table, why are you starting from the Hold Assigned date of 1/3/2020?  From your first table, i can see that there are other dates as well which meet the condition of  Hold Assigned < Hold Unassigned at the end of each month.  An example being the first row of the first table i.e. 1/1/2020 and 3/1/2020.

[MorePowerBI]

The second table shows the total cost on hold at the end of the month.

The records in the third table are within scope of the total cost on hold at the end of May 2020 because you have lots that were placed on hold on or before May AND they were released from hold after May.

The first row of the first table wouldn't be within scope of the total cost on hold at the end of May 2020 because the record was released on March 2020.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[MorePowerBI]

This works like a charm. One question though - how did you sort the resulting table in chronological order? Sorting by either Year or Month doesn't do the trick.

[Ashish_Mathur]

Thank you.  If my previous reply helped, please mark it as Answer.  There's nothing i did for that.  In the Calendar Table, i sorted the Month Name field by the Month number field.