topic Create a KPI target based on the previous year value in DAX Commands and Tips

Create a KPI target based on the previous year value

game1 — Fri, 01 Mar 2024 14:28:36 GMT

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

Re: Create a KPI target based on the previous year value

Anonymous — Fri, 09 Feb 2024 03:45:38 GMT

Hi game1 ,

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

Best Regards

Re: Create a KPI target based on the previous year value

Anonymous — Fri, 09 Feb 2024 06:34:04 GMT

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

Best Regards

Re: Create a KPI target based on the previous year value

game1 — Fri, 01 Mar 2024 14:34:00 GMT

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!

Re: Create a KPI target based on the previous year value

Anonymous — Mon, 12 Feb 2024 08:52:43 GMT

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

Re: Create a KPI target based on the previous year value

game1 — Tue, 13 Feb 2024 05:15:42 GMT

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!

Re: Create a KPI target based on the previous year value

Anonymous — Mon, 19 Feb 2024 09:20:10 GMT

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