Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi
I need to calculate the sum of Values based on the parameters shown on the attached file. The slicers are shown on the left side (see below).
The 2 date fields are inputs based on the [Proj_Create_Date].
Week Diff is the difference between the two selected dates
Future_Weeks is a drop down select list based on the [Projected_Week]
In PBI:
I have unpivoted the week values as shown in the blue table.
Also created Min and Max measures to calculate the [Week_Diff] using DateDiff function.
[Future Weeks] is a drop down list of the [Projected_Week].
Added a [Proj_Week_Date] which shows the actual date of the week based on the [Projected_Week] number
The requirements are:
- calculate the sum of the value of an item,
based on the selected [First Week] and the [Week_Diff] value. So if the selected 2 days are as shown above then the week diff is 2 weeks. Then, I need to sum the values in 14/07/2024 (First Week selected) line for week numbers 1 and 2.
Eg1: so if we take Item number 2 as an example, it should be 140.
Eg2: If the date parameters were set to [First Week] = 07/07/2024 and [Last Week] = 21/07/2024, [Week_Diff] is 3.
Then for Item number 2 the result should be 70 (sum of week (1, 2, and 3).
- Calculate the sum of an item
Same logic as above, but now also add the extra weeks based on the [Future Weeks] selected. So as in the above Eg1, add another 5 weeks (as that's what's selected) to the 2 weeks' result (sum of 7 weeks).
Eg3: So for Item 2 it'll be a total of 270.
Hope someone is able to shed some light on how these can be calculated.
Thank you!
Solved! Go to Solution.
Hi @msdf ,
Create a disconnect table with dates and another for the future week.
Now add the following measures:
Item Value = SUM('Fact'[Value])
Date Diff =
DATEDIFF(
MIN(DAtes[Projected_Week_Date]),
MAX(DAtes[Projected_Week_Date]),
WEEK
)
Total Item Value =
VAR temptable = FILTER(
'Fact',
'Fact'[Proj_Create_Date] = MIN(DAtes[Projected_Week_Date]) && 'Fact'[Projected_Week] <= [Date Diff]
)
RETURN
SUMX(
temptable,
'Fact'[Value]
)
Total Item Value Future Week =
VAR temptable = FILTER(
'Fact',
'Fact'[Proj_Create_Date] = MIN(DAtes[Projected_Week_Date]) && 'Fact'[Projected_Week] <= [Date Diff] + SELECTEDVALUE(Future[Future_Week])
)
RETURN
SUMX(
temptable,
'Fact'[Value]
)
Please see file attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @msdf ,
Create a disconnect table with dates and another for the future week.
Now add the following measures:
Item Value = SUM('Fact'[Value])
Date Diff =
DATEDIFF(
MIN(DAtes[Projected_Week_Date]),
MAX(DAtes[Projected_Week_Date]),
WEEK
)
Total Item Value =
VAR temptable = FILTER(
'Fact',
'Fact'[Proj_Create_Date] = MIN(DAtes[Projected_Week_Date]) && 'Fact'[Projected_Week] <= [Date Diff]
)
RETURN
SUMX(
temptable,
'Fact'[Value]
)
Total Item Value Future Week =
VAR temptable = FILTER(
'Fact',
'Fact'[Proj_Create_Date] = MIN(DAtes[Projected_Week_Date]) && 'Fact'[Projected_Week] <= [Date Diff] + SELECTEDVALUE(Future[Future_Week])
)
RETURN
SUMX(
temptable,
'Fact'[Value]
)
Please see file attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
Thank you so much for the above solution. I did have to change the fields around a bit on the calcs, but once I got my head around it, it worked like a charm. I also got to learn about disconnected tables. My new favourite way to use DAX.
Thanks for your help!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
68 | |
53 | |
39 | |
33 |
User | Count |
---|---|
70 | |
63 | |
57 | |
49 | |
46 |