Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a fact table with a StartDate, EndDate and amount (in this case FTE). I want to "pretend" like the end date doesn't exist and prolong the amount in the feature. See the example below of what I want to achieve (the red letters).
I have tried all kind of calculations but I don't seem to get the right result. Any suggestions?
Solved! Go to Solution.
Hi @WNT ,
Please try the following formula to create a new table firstly:
New Table =
TOPN (
12,
DISTINCT (
SELECTCOLUMNS (
CALENDAR ( MIN ( 'Fact'[StartDatumA] ), MAX ( 'Fact'[EindDatumA] ) ),
"YearMonth",
YEAR ( [Date] ) * 100
+ MONTH ( [Date] ),
"Month", FORMAT ( [Date], "MMMM" ),
"Value",
LOOKUPVALUE (
'Table'[_D3],
'Table'[Maand], FORMAT ( [Date], "MMMM" ),
'Table'[Jaar], YEAR ( [Date] )
)
)
)
)
Then add the measure:
Measure =
IF (
MAX ( 'Table'[Jaar] ) > YEAR ( MAX ( 'Fact'[StartDatumA] ) ),
CALCULATE (
MIN ( 'New Table'[Value] ),
FILTER ( 'New Table', 'New Table'[Month] = MAX ( 'Table'[Maand] ) )
)
)
The final output is shown below:
Here is the pbix file.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-eqin-msft thanks for the reply! I'll try to duplicate it in my own environement this weekend and let you know if this is the solution
Hi @WNT ,
Please try the following formula to create a new table firstly:
New Table =
TOPN (
12,
DISTINCT (
SELECTCOLUMNS (
CALENDAR ( MIN ( 'Fact'[StartDatumA] ), MAX ( 'Fact'[EindDatumA] ) ),
"YearMonth",
YEAR ( [Date] ) * 100
+ MONTH ( [Date] ),
"Month", FORMAT ( [Date], "MMMM" ),
"Value",
LOOKUPVALUE (
'Table'[_D3],
'Table'[Maand], FORMAT ( [Date], "MMMM" ),
'Table'[Jaar], YEAR ( [Date] )
)
)
)
)
Then add the measure:
Measure =
IF (
MAX ( 'Table'[Jaar] ) > YEAR ( MAX ( 'Fact'[StartDatumA] ) ),
CALCULATE (
MIN ( 'New Table'[Value] ),
FILTER ( 'New Table', 'New Table'[Month] = MAX ( 'Table'[Maand] ) )
)
)
The final output is shown below:
Here is the pbix file.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
92 | |
77 | |
71 | |
64 | |
58 |
User | Count |
---|---|
106 | |
97 | |
76 | |
63 | |
61 |