Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 |
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
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.
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
96 | |
90 | |
81 | |
69 |
User | Count |
---|---|
157 | |
125 | |
116 | |
111 | |
95 |