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.
Dear all,
I am currently struggling with a DAX calculation.
The objective is to conduct a dynamic calculation related to weekly time intervals.
The starting point is a dynamic value showing today's status of Total number (this has been calculated by a measure "start1", in the example below displayed with value 31). This starting value is the input for a further dynamic calculation per calendar week (see logic chart below).
Calender week/year | Delta Inbound_Outbound | Total number |
| 0 | 31 (dynamic starting value calculated per Measure) |
08/21 | -1 | 31+(-1) = 30 |
09/21 | 2 | 30 +2 = 32 |
10/21 | 3 | 32 + 3 = 35 |
11/21 | 1 | 35 + 1 = 36 |
12/21 | -1 | 36 + (-1) = 35 |
13/21 | 0 | 35 + 0 = 35 |
The calculated Total number within a week (starting with the current week) is always the new input for the upcoming week’s calculation. With the formula/measure I am using at the moment the input for the calculation is always the starting value also for each following week (-> 31) which results in the incorrect Total number per week.
I would be very thankful if you could support in fixing the DAX calculation/measure!
i am sure it can be neatened up but i think this is what you want.
you will need to fix up the first two variables to your current week.
its essentially a running total starting at the current week???
measure:=
var StartingYearWeek="21-07"
var StartingValue=31
var ThisYearWeek=MAX(Table1[year-week])
var X = CALCULATE(SUM([Delta]),
FILTER(all(Table1),
Table1[year-week]>StartingYearWeek && Table1[year-week]<=ThisYearWeek
)
)
return IF(ThisYearWeek>StartingYearWeek,
X+StartingValue,
BLANK())
t
@Morfin thanks for the reply and support. Unfortunately the Starting value as well as the StartingYearWeek are not fix values and will dynamically change.
Maybe I have to specify my problem formulation in order to solve the issue.
The following table displays the problem more precise.
I want to take the sum (No. of Parts, yellow line) until the current week/date (here CW08/21 or Today) and afterwards take this measure as input for a further calculation starting from this week/Today on (orange column).
It should be dynamic, meaning the input sum value (yellow line) as well as the Total number (orange column) will change over the course of time each week since I am always referring to the current week/date (next week 09/21...).
How can I do that and what exact DAX calculation can solve this issue.
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 |
---|---|
45 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
70 | |
48 | |
45 | |
19 | |
16 |