cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## End of Month Total Cost on Hold

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
1 ACCEPTED SOLUTION
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
5 REPLIES 5
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

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.

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

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.

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors