Forum Discussion

game1's avatar
game1
Icon for Helper III rankHelper III
2 years ago
Solved

Create a KPI target based on the previous year value

For example, if I have 10000 items sold in 2022, and if I want my target in 2023, I must do : 10000 * 1.8. I have: table1[items], table1[date]  items date  car     2022/06/19   table  2023/10/11 ...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi game1 ,

    I updated the sample pbix file, please find the details in the attachment.

    Target = 
VAR _year =
    SELECTEDVALUE('Date'[Date].[Year] )
VAR _month =
    SELECTEDVALUE('Date'[Date].[MonthNo],12)
VAR _pycount =
    CALCULATE (
        COUNT ( 'Table'[items] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            YEAR ( 'Table'[date] ) = _year-1
                &&  IF(ISFILTERED('Date'[Date].[Month]),MONTH ( 'Table'[date] )  = _month,1=1)
        )
    )
RETURN
       _pycount  * 1.8

    Best Regards

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi game1 ,

What's your expected result? Could you please provide more details base on your shared sample data?

 

Best Regards

game1's avatar
game1
Icon for Helper III rankHelper III
2 years ago

Hi, 

It is possible to do the same thing by using Table[date]? Because, what I need is that if I select a year or month from Table[date], it must calculated my Target.  Thanks!

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi game1 ,

    When apply the date field of your fact table, it will only display the data with the current selected month. Hence it will not return the expected result...

    VAR _year =
    SELECTEDVALUE('Table'[Date].[Year] )
VAR _month =
    SELECTEDVALUE('Table'[Date].[MonthNo],12)

    Best Regards