topic Last year for non-existing date in DAX Commands and Tips

Last year for non-existing date

maricci — Wed, 27 Dec 2023 11:13:15 GMT

I have a table with 10.000 rows that looks like:

Date	Numb	Amount now	Amount previous year
31. 12. 2023.	88	250	200
31. 12. 2023.	75	150	105
31. 12. 2022.	88	200	356
31. 12. 2022.	75	105	253
31. 12. 2022.	32	300	253

For a certain date with a certain number, we need the amount for the current and previous year, but the problem arises when we have a certain date with a certain number in the last year, but not in the current year. We need that date in the new year, 0 for the current amount and the past amount to withdraw from last year. Zero padding is not possible, because the table is very large.

Date	Numb	Amount now	Amount previous year
31. 12. 2023.	88	250	200
31. 12. 2023.	75	150	105
31. 12. 2023.	32	0	300
31. 12. 2022.	88	200	356
31. 12. 2022.	75	105	253
31. 12. 2022.	32	300	253

Re: Last year for non-existing date

Fowmy — Wed, 27 Dec 2023 11:39:55 GMT

maricci

With the provided limited scope of the data, I assume this solution should work or provide an idea atleast.
Create a new table with this code:

New TAble = ADDCOLUMNS( CROSSJOIN( ALLNOBLANKROW( Table04[Date] ) , ALLNOBLANKROW( Table04[Numb] ) ), "@CurrentAmount" , CALCULATE( SUM(Table04[Amount now] ) ), "@PreviousYearAmount" , VAR __Date = Table04[Date] VAR __Numb = Table04[Numb] RETURN MAXX( FILTER( table04 , Table04[Date] = EDATE( __Date , -12 ) && Table04[Numb] = __Numb ), Table04[Amount now] ) )

Re: Last year for non-existing date

maricci — Thu, 28 Dec 2023 08:40:50 GMT

It works somewhat, but I have a problem, it doesn't pull the data if I don't have any data for that date, it won't create it, to show the previous year, like for example. in the example 31.12.2018. numb 64. It should also pull the date for me only if it has the previous year, it pulls a lot of blank lines here. As for example 31.12.2021. numb 64, the previous year appears in 2022, which is okay, but then a blank line appears in 2023 as well. Thank you.

Created Table

DateNumb@CurrentAmount@PreviousYearAmount

31.12.2023.	75	150	105
31.12.2023.	64
31.12.2023.	88	250	200
31.12.2023.	53
31.12.2023.	32		300
31.12.2022.	64		101
31.12.2022.	88	200	356
31.12.2022.	75	105	253
31.12.2022.	53
31.12.2022.	32	300
31.12.2021.	88	356	406
31.12.2021.	75	253	233
31.12.2021.	64	101
31.12.2021.	53
31.12.2021.	32
31.12.2020.	88	406
31.12.2020.	75	233
31.12.2020.	64
31.12.2020.	53
31.12.2020.	32
31.10.2019.	88
31.10.2019.	75
31.10.2019.	64
31.10.2019.	53	233
31.10.2019.	32
31.12.2018.	88
31.12.2018.	75
31.12.2018.	64	101
31.12.2018.	53
31.12.2018.	32

Table with data

Date NumbAmount nowAmount last year

nedelja, 31. decembar 2023.	88	250	200
nedelja, 31. decembar 2023.	75	150	105
subota, 31. decembar 2022.	88	200	356
subota, 31. decembar 2022.	75	105	253
subota, 31. decembar 2022.	32	300	253
petak, 31. decembar 2021.	88	356	406
petak, 31. decembar 2021.	64	101	102
petak, 31. decembar 2021.	75	253	233
četvrtak, 31. decembar 2020.	88	406	456
četvrtak, 31. decembar 2020.	75	233	333
četvrtak, 31. oktobar 2019.	53	233	333
ponedeljak, 31. decembar 2018.	64	101	102

Re: Last year for non-existing date

sjoerdvn — Thu, 28 Dec 2023 14:23:56 GMT

Hi,
A more typical aproach in Power BI would be to skip that "Amount previous year" column altogether and use "current" and "previous year"measures. You really don't need to add extra rows. Check out time intelligence functions like PREVIOUSYEAR for this. Easiest is to leave the current value blank if there's only last year, but it is easy to tweak it to showing zero's.