Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I need a formula that takes into account Start Date and End Date in relation to a "Selected Date" and gives a Total Sum.
I have a data set that includes the Start/End Dates, Site, Object, and ObjectAttributeX (a number equal to or less than 1).
I want to "Selected Date" to come from a Slicer for a Date Dimension.
From the Start Date, the ObjectAttributeX counts for every month thereafter until the End Date.
If an Object's AttributeX changes, an End Date is added and a new line with a new Start Date and the new AttributeX is added.
Ex - raw data:
Line# | Start Date | End Date | Site | Object | ObjectAttributeX |
1 | 7/1/2019 | 5 | A | 0.90 | |
2 | 7/1/2019 | 5 | B | 1.00 | |
3 | 7/1/2019 | 2/29/2020 | 5 | C | 0.50 |
4 | 3/1/2020 | 5 | C | 0.20 | |
5 | 7/1/2019 | 3/31/2020 | 5 | D | 1.00 |
6 | 4/1/2020 | 5 | D | 0.80 | |
7 | 7/1/2019 | 5 | E | 0.75 | |
8 | 7/1/2019 | 12/31/2019 | 5 | F | 0.85 |
9 | 7/1/2019 | 5 | G | 0.25 |
For instance, if I selected from a Slicer:
July 2019 Total ObjectAttributeX = 5.25
Line# | Start Date | End Date | Site | Object | ObjectAttributeX |
1 | 7/1/2019 | 5 | A | 0.90 | |
2 | 7/1/2019 | 5 | B | 1.00 | |
3 | 7/1/2019 | 2/29/2020 | 5 | C | 0.50 |
5 | 7/1/2019 | 3/31/2020 | 5 | D | 1.00 |
7 | 7/1/2019 | 5 | E | 0.75 | |
8 | 7/1/2019 | 12/31/2019 | 5 | F | 0.85 |
9 | 7/1/2019 | 5 | G | 0.25 |
March 2020 Total Object AttributeX = 4.10
Line# | Start Date | End Date | Site | Object | ObjectAttributeX |
1 | 7/1/2019 | 5 | A | 0.90 | |
2 | 7/1/2019 | 5 | B | 1.00 | |
4 | 3/1/2020 | 5 | C | 0.20 | |
5 | 7/1/2019 | 3/31/2020 | 5 | D | 1.00 |
7 | 7/1/2019 | 5 | E | 0.75 | |
9 | 7/1/2019 | 5 | G | 0.25 |
April 2020 Total Object AttributeX = 3.90
Line# | Start Date | End Date | Site | Object | ObjectAttributeX |
1 | 7/1/2019 | 5 | A | 0.90 | |
2 | 7/1/2019 | 5 | B | 1.00 | |
4 | 3/1/2020 | 5 | C | 0.20 | |
6 | 4/1/2020 | 5 | D | 0.80 | |
7 | 7/1/2019 | 5 | E | 0.75 | |
9 | 7/1/2019 | 5 | G | 0.25 |
So when March 2020 is selected in the Slicer, I need the formula to use Line #5 for Object D. When April 2020 is selected in the Slicer, I need the formula to use Line #6 for Object D.
I have a DateDimension, Site Key, and another Table of info already created, this is the relationships/directions for those:
Solved! Go to Solution.
Hi @kressb ,
No relationship between your Date Dimension table and your Fact table.
Then create measures like so:
Measure =
VAR SelectedYear =
SELECTEDVALUE ( 'Date Dimension Table'[Year] )
VAR SelectedMonth =
SELECTEDVALUE ( 'Date Dimension Table'[Month] )
VAR LastDayofSelectedYearMonth =
EOMONTH ( DATE ( SelectedYear, SelectedMonth, 1 ), 0 )
RETURN
SWITCH (
TRUE (),
SelectedYear = BLANK (), 1,
MAX ( 'Fact Table'[Start Date] ) <= LastDayofSelectedYearMonth
&& MAX ( 'Fact Table'[End Date] ) = BLANK (), 1,
MAX ( 'Fact Table'[Start Date] ) <= LastDayofSelectedYearMonth
&& MAX ( 'Fact Table'[End Date] ) > LastDayofSelectedYearMonth, 1
)
Total =
CALCULATE (
SUM ( 'Fact Table'[ObjectAttributeX] ),
FILTER ( 'Fact Table', [Measure] = 1 )
)
Then, you can put [Measure] as "Filters on this visual" and set "is 1".
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @kressb ,
No relationship between your Date Dimension table and your Fact table.
Then create measures like so:
Measure =
VAR SelectedYear =
SELECTEDVALUE ( 'Date Dimension Table'[Year] )
VAR SelectedMonth =
SELECTEDVALUE ( 'Date Dimension Table'[Month] )
VAR LastDayofSelectedYearMonth =
EOMONTH ( DATE ( SelectedYear, SelectedMonth, 1 ), 0 )
RETURN
SWITCH (
TRUE (),
SelectedYear = BLANK (), 1,
MAX ( 'Fact Table'[Start Date] ) <= LastDayofSelectedYearMonth
&& MAX ( 'Fact Table'[End Date] ) = BLANK (), 1,
MAX ( 'Fact Table'[Start Date] ) <= LastDayofSelectedYearMonth
&& MAX ( 'Fact Table'[End Date] ) > LastDayofSelectedYearMonth, 1
)
Total =
CALCULATE (
SUM ( 'Fact Table'[ObjectAttributeX] ),
FILTER ( 'Fact Table', [Measure] = 1 )
)
Then, you can put [Measure] as "Filters on this visual" and set "is 1".
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
79 | |
61 | |
60 | |
58 |
User | Count |
---|---|
151 | |
113 | |
99 | |
80 | |
72 |