Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
game1
Helper III
Helper III

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 

 car     2022/06/13 

 house 2023/02/11 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

vyiruanmsft_0-1708334323536.png

Best Regards

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Hi @game1 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

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
                && MONTH ( 'Table'[date] ) <= _month
        )
    )
RETURN
    _pycount * 1.8

vyiruanmsft_0-1707460413723.png

Best Regards

Hello,

It is only working for Year, not for Month. For example, I should have for october 2023, 1.8 x 1 = 1.8, because it should count the items of october, for the previous year. In october 2022, we have 1 item. So, the target for ocotber 2023, must be : 1.8 x 1 = 1.8. It I select only Year, it work perfectly, but month is not giving the good result. Thanks!

 

Capture d’écran, le 2024-02-13 à 00.10.14.png

Anonymous
Not applicable

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

vyiruanmsft_0-1708334323536.png

Best Regards

Anonymous
Not applicable

Hi @game1 ,

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

 

Best Regards

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
Not applicable

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.