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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Users online (1,164)