Forum Discussion

eraldo_DP's avatar
eraldo_DP
Frequent Visitor
6 years ago
Solved

How to calculate target by data?

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's avatar
    mahoneypat
    6 years ago

    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

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    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's avatar
      eraldo_DP
      Frequent Visitor
      6 years ago

      Perfect, thanks for this solution.

  • AntrikshSharma's avatar
    AntrikshSharma
    Icon for Community Champion rankCommunity Champion
    6 years ago

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

    • eraldo_DP's avatar
      eraldo_DP
      Frequent Visitor
      6 years ago

      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.

  • Harpreet1405_12's avatar
    Harpreet1405_12
    Icon for Helper III rankHelper III
    3 years ago

    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