How to calculate target by data?

[eraldo_DP]

Hi Everyone,

How i can calculate the target corresponding to the date and group?

https://1drv.ms/u/s!As-s3TNexQ8TipkPATGyYTW6Fbr-qA?e=gLg3Ev  

[mahoneypat · Accepted solution]

Here is a measure expression that returns the Target for the Date and Group on that row of the table.

GroupDate Target =
VAR __thisdate =
    SELECTEDVALUE ( Sales[Date] )
VAR __thisGroup =
    SELECTEDVALUE ( Sales[Group] )
RETURN
    CALCULATE (
        MAX ( Target[Target Conversation] ),
        ALL ( Target ),
        Target[StartDate] <= __thisdate,
        Target[EndDate] >= __thisdate,
        Target[Group] = __thisGroup
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Harpreet1405_12]

Hello,

I need your help to set the targets for the upcoming months (Oct 2022- SEPT 2023) BASED on the historical data,  I have the historical data from 2019 to 2022, so we go like Oct 2020-Sept 2021, Oct 2021-Sept 2022, Oct 2022-Sept 2023(I want to set the targets for these months) for 80 different programs or categories.

Is There any formula or any Dax function available.

I will highly appreciate the response.

Thank you

[mahoneypat · Accepted solution]

Here is a measure expression that returns the Target for the Date and Group on that row of the table.

GroupDate Target =
VAR __thisdate =
    SELECTEDVALUE ( Sales[Date] )
VAR __thisGroup =
    SELECTEDVALUE ( Sales[Group] )
RETURN
    CALCULATE (
        MAX ( Target[Target Conversation] ),
        ALL ( Target ),
        Target[StartDate] <= __thisdate,
        Target[EndDate] >= __thisdate,
        Target[Group] = __thisGroup
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[eraldo_DP · Accepted solution]

Perfect, thanks for this solution.

[AntrikshSharma]

You will have to explain more, not sure what you are trying to do.

[eraldo_DP]

I need returne in columm "Target_Conversation" the target active for the period. Example, in table "Target" have the columm name "Group" and others columns with Start date and End date for the group.

Then the "Group1" has 6% of Target with start 2020/04/01 and finish in 2020/04/05, i need return the value Target active for date for comparison the value realized and value projected.