Dynamic goals measures from DB table in power bi using DAX

[Narukkp]

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

[Narukkp]

Hi Team,

Any one of having any idea about above ask ?

[v-deddai1-msft · Accepted 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