Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Team,
I have 3 tables one is below goal table , one is Date table(Dax table) and another one is DB reporting table ( Relation between Reporting & Date table using date column). Now my requirement is based on date(Date table) slicer selection goal should be display. i.e. if I am selected the range up to Feb2019 then it should be show as 0.82 same way I am selected range up to Jun2019 then it should be show as 0.85. Finally my aim is goals should be displayed dynamically from goal table based on date slicer selection with in the range. Could you please help me on this scenario.
KPI Goal Desc | Code | KPI Goal Value | Effect StartDate | Effect End Date |
Adherence | CMV | 0.82 | 19000101 | 20190430 |
Adherence | CMV | 0.85 | 20190501 | 20190731 |
Adherence | CMV | 0.8 | 20190801 | 20191231 |
Adherence | CMV | 0.87 | 20200101 | 99991231 |
Solved! Go to Solution.
Hi @Narukkp ,
I suggest you create two calculated column to convert "yyyymmdd" to date type:
ESD =
IFERROR (
DATE ( LEFT ( 'Goal'[Effect StartDate], 4 ), MID ( 'Goal'[Effect StartDate], 5, 2 ), RIGHT ( 'Goal'[Effect StartDate], 2 ) ),
BLANK ()
)
EED =
IFERROR (
DATE ( LEFT ( 'Goal'[Effect End Date], 4 ), MID ( 'Goal'[Effect End Date], 5, 2 ), RIGHT ( 'Goal'[Effect End Date], 2 ) ),
BLANK ()
)
Would you please try to create the measure below to get the dynamic goal:
goal1 =
VAR max1 =
CALCULATE ( MAX ( 'date'[Date] ), ALLSELECTED ( 'date' ) )
VAR min1 =
CALCULATE ( MIN ( 'date'[Date] ), ALLSELECTED ( 'date' ) )
RETURN
CALCULATE (
MIN ( 'Goal'[KPI Goal Value] ),
FILTER ( 'Goal', 'Goal'[ESD] <= min1 && 'Goal'[EED] >= max1 )
)
Please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EbD4WyGScEBKr6xbY5...
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi Team,
Any one of having any idea about above ask ?
Hi @Narukkp ,
I suggest you create two calculated column to convert "yyyymmdd" to date type:
ESD =
IFERROR (
DATE ( LEFT ( 'Goal'[Effect StartDate], 4 ), MID ( 'Goal'[Effect StartDate], 5, 2 ), RIGHT ( 'Goal'[Effect StartDate], 2 ) ),
BLANK ()
)
EED =
IFERROR (
DATE ( LEFT ( 'Goal'[Effect End Date], 4 ), MID ( 'Goal'[Effect End Date], 5, 2 ), RIGHT ( 'Goal'[Effect End Date], 2 ) ),
BLANK ()
)
Would you please try to create the measure below to get the dynamic goal:
goal1 =
VAR max1 =
CALCULATE ( MAX ( 'date'[Date] ), ALLSELECTED ( 'date' ) )
VAR min1 =
CALCULATE ( MIN ( 'date'[Date] ), ALLSELECTED ( 'date' ) )
RETURN
CALCULATE (
MIN ( 'Goal'[KPI Goal Value] ),
FILTER ( 'Goal', 'Goal'[ESD] <= min1 && 'Goal'[EED] >= max1 )
)
Please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EbD4WyGScEBKr6xbY5...
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |