Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm currently working on a measure that calculates the cumulative budget allocation with mismatched granularities.
I used this Blog by Enterprise DNA to get the formulations of my measures.
Also important to note that I've described my problem in more detail in my previous post but the solution created a different issue for me.
This is how my data is set up:
This is the problem I'm running into:
[BudgetAllocation] is fine if I wanted the individual days, but I want it cumulatively.
[BudgetAllocation2] was my attempt to make it cumulative, but it really just produced the entire value for the month.
[BudgetAllocation3] was another attempt to make it cumulative but because I have the YearMonth = YearMonth, it brings the sum back to zero at the start of each month.
My 3 measures are as follows:
Hi @v-tangjie-msft ,
Thank you for your response, but that assumes that the budgets are linear. We can assume that the budget is linear within each day of the month, but each month has a different budget.
These are roughly the formulations I'm looking for:
From my second screenshot in my original post you can see the [BudgetAllocation] measure provides the daily budget for each month (shown as [April Daily Budget] in the screenshot above). [BudgetAllocation 3] does basically what [Cumulative April Daily Budget] does, but doesn't consider the months prior.
Date | BudgetAllocation | BudgetAllocation 2 | BudgetAllocation 3 | CumulativeBudgetAllocation |
1-Apr-22 | 655.68 | 12457.87 | 655.68 | 655.68 |
4-Apr-22 | 655.68 | 12457.87 | 1311.36 | 1311.36 |
5-Apr-22 | 655.68 | 12457.87 | 1967.03 | 1967.04 |
6-Apr-22 | 655.68 | 12457.87 | 2622.71 | 2622.72 |
7-Apr-22 | 655.68 | 12457.87 | 3278.39 | 3278.4 |
8-Apr-22 | 655.68 | 12457.87 | 3934.07 | 3934.08 |
11-Apr-22 | 655.68 | 12457.87 | 4589.74 | 4589.76 |
12-Apr-22 | 655.68 | 12457.87 | 5245.42 | 5245.44 |
13-Apr-22 | 655.68 | 12457.87 | 5901.1 | 5901.12 |
14-Apr-22 | 655.68 | 12457.87 | 6556.78 | 6556.8 |
19-Apr-22 | 655.68 | 12457.87 | 7212.45 | 7212.48 |
20-Apr-22 | 655.68 | 12457.87 | 7868.13 | 7868.16 |
21-Apr-22 | 655.68 | 12457.87 | 8523.81 | 8523.84 |
22-Apr-22 | 655.68 | 12457.87 | 9179.49 | 9179.52 |
25-Apr-22 | 655.68 | 12457.87 | 9835.16 | 9835.2 |
26-Apr-22 | 655.68 | 12457.87 | 10490.84 | 10490.88 |
27-Apr-22 | 655.68 | 12457.87 | 11146.52 | 11146.56 |
28-Apr-22 | 655.68 | 12457.87 | 11802.2 | 11802.24 |
29-Apr-22 | 655.68 | 12457.87 | 12457.87 | 12457.92 |
2-May-22 | 608.2 | 12772.28 | 608.2 | 13066.12 |
3-May-22 | 608.2 | 12772.28 | 1216.41 | 13674.32 |
4-May-22 | 608.2 | 12772.28 | 1824.61 | 14282.52 |
5-May-22 | 608.2 | 12772.28 | 2432.82 | 14890.72 |
6-May-22 | 608.2 | 12772.28 | 3041.02 | 15498.92 |
9-May-22 | 608.2 | 12772.28 | 3649.22 | 16107.12 |
10-May-22 | 608.2 | 12772.28 | 4257.43 | 16715.32 |
I'm looking for the [CumulativeBudgetAllocation]. I've highlighted where [BudgetAllocation3] and what I'm looking for start to differ on the first workday of May (2-May-22).
Hi @Anonymous ,
According to your description, you want to You want to accumulate calculations [Budget]. Here are my steps you can follow as a solution.
(1)My test data comes from the example data you provided in the previous post. Re: Nonlinear Budget Release Measure - Microsoft Power BI Community
(2)We can create a measure.
BudgetAllocation =
var _slice=SELECTEDVALUE('Dates'[DateKey])
var _table=FILTER(ALL('Budget Table'),'Budget Table'[DateKey] <= _slice)
return
SUMX(_table,'Budget Table'[Budget ])
(3)Then the result is as follows.
If this method does not meet your needs, you can provide us with detailed input and output examples in tabular form so that we can better solve the problem for you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |