Forum Discussion
In a matrix, how I can replace values in empty cells with values of previous cells ?
mape,
See my formula I have used date field there (my date field format is DD/MM/YYYY) and extended it to YEAR using DAX
- Sheet6[End].[Year] <------
So do the same if you will use year only then it will calculate all values for the year. If you dont have a date field then concatenate your day/month/year columns and then use it.
Regards
Abduvali
Hi Abduvali, I see but the proble is the same. If I replaced that field by a full date (named "Fecha") , finally the matrix shows the same result that I got before:
lastyear =
var l = SUM(Ventas[ImporteVenta])
var c = CALCULATE(SUM(Ventas[ImporteVenta]);FILTER(ALLSELECTED(Ventas[Fecha].[year]);Ventas[Fecha].[Year]-1))
return
if(l=BLANK();c;l)
the matrix result like:
| Nombre | 2016 | 2017 | Total |
| Customer1 | 37001 | 37001 | 74002 |
| Customer2 | 37001 | 37001 | 74002 |
| Customer3 | 37001 | 37001 | 74002 |
| Customer4 | 37001 | 37001 | 74002 |
| Customer5 | 37001 | 37001 | |
| Customer6 | 37001 | 37001 | 74002 |
| Customer7 | 37001 | 37001 | |
| Customer8 | 37001 | 37001 | 74002 |
| Total | 296008 | 222006 | 518014 |
Hi Abduvali , sorry for my english, I think that we don't speak about the same.
Assuming there is only one sale per year per customer and in some cases if there is not one I want to take the previous year, I do not care about the date, month, day ... only the year. It keeps showing me what we are talking about, removing the rest of the elements from the date hierarchy
Look the image:
mape,
You have to understand the solution, the following DAX is built on MM/DD/YYYY date format field so to make it easier follow the link to my Power BI library and download the solution =D
Hope this helps and will explain how it works.
Regards
Abduvali