Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
MorePowerBI
Helper II
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 AssignedHold UnassignedCost
1/1/20203/1/2020$27,633.31
1/1/20202/1/2020$3,539,210.76
1/1/20201/1/2020$3,377,478.80
2/1/20205/1/2020$162,544.44
2/1/20204/1/2020$5,631.62
2/1/20203/1/2020$387,690.03
2/1/20202/1/2020$4,340,867.64
3/1/20207/1/2020$319,570.85
3/1/20206/1/2020$900,650.89
3/1/20205/1/2020$867,777.66
3/1/20204/1/2020$4,766,452.98
3/1/20203/1/2020$9,953,468.13
4/1/20208/1/2020$362,153.01
4/1/20207/1/2020$384,938.27
4/1/20206/1/2020$356,583.83
4/1/20205/1/2020$1,890,412.76
4/1/20204/1/2020$3,758,262.05
5/1/20209/1/2020$92,215.48
5/1/20207/1/2020$88,314.70
5/1/20206/1/2020$4,856,607.76
5/1/20205/1/2020$7,894,614.51
6/1/20209/1/2020$256,110.61
6/1/20208/1/2020$966,163.58
6/1/20207/1/2020$3,373,959.95
6/1/20206/1/2020$7,184,874.89
7/1/202012/1/2020$328,905.39
7/1/20209/1/2020$250,091.26
7/1/20208/1/2020$2,389,127.12
7/1/20207/1/2020$9,020,577.40
8/1/202012/1/2020$662,568.93
8/1/202011/1/2020$137,960.85
8/1/20209/1/2020$379,822.07
8/1/20208/1/2020$5,552,546.98
9/1/202012/1/2020$645,260.02
9/1/202011/1/2020$36,195.80
9/1/202010/1/2020$1,838,781.34
9/1/20209/1/2020$4,592,379.78
10/1/202012/1/2020$377,350.39
10/1/202011/1/2020$799,488.08
10/1/202010/1/2020$6,383,927.56
11/1/20202/1/2021$312,846.68
11/1/20201/1/2021$1,067,142.22
11/1/202012/1/2020$6,113,607.14
11/1/202011/1/2020$5,207,123.22
12/1/20201/1/2021$384,195.73
12/1/202012/1/2020$2,810,326.36
1/1/20213/1/2021$51,608.07
1/1/20212/1/2021$937,482.94
1/1/20211/1/2021$1,733,842.25
2/1/20213/1/2021$1,002,841.07
2/1/20212/1/2021$5,089,770.61
3/1/20214/1/2021$154,740.15
3/1/20213/1/2021$4,511,765.26
4/1/20215/1/2021$552,035.22
4/1/20214/1/2021$6,985,082.55
4/1/2021 $470,423.17

 

 

And this is the expected result:

End of MonthTotal 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 AssignedHold UnassignedCost
3/1/20207/1/2020$319,570.85
3/1/20206/1/2020$900,650.89
4/1/20208/1/2020$362,153.01
4/1/20207/1/2020$384,938.27
4/1/20206/1/2020$356,583.83
5/1/20209/1/2020$92,215.48
5/1/20207/1/2020$88,314.70
5/1/20206/1/2020$4,856,607.76
1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

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.

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

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
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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