Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi everyone!! I need help about this...
Measure of sales per year and in case there is no information for that year, show the sales of the previous year.
That is, given a series of sales per year for a series of customers, it would be necessary if, for a given year, that customer does not have a value, to fill the cell with the value of the last year that had value.
Example
Customer A (SALES 2017 = 1000, SALES 2016 = 1200, SALES 2015 = 900)
Client B (SALES 2016 = 500, SALES 2015 = 700)
In a matrix of sales of customer x year the crossing of Client B and 2017 would be empty and what I want is for it to take the value of the previous year that contains data, specifically 2016), that is to say that the table will remain as follows:
2015 2016 2017
Customer A 900 1200 1000
Client B 700 500 500
That value of 500 in 2017 I would have to put my measurement.
Thank you very much!!!!
Hi @mape,
You can create a Column with IF statement in it like:
Regards
Abduvali
Hi @Abduvali, thanks for you quick response, in fact the result isn't as I expected.
By default with the field "Sales" I have this matrix:
2015 | 2016 | 2017 | |
Customer A | 900,00 € | 1.200,00 € | 1.000,00 € |
Customer B | 700,00 € | 500,00 € |
The new measure or calculated column that I want , it shows like this:
2015 | 2016 | 2017 | |
Customer A | 900,00 € | 1.200,00 € | 1.000,00 € |
Customer B | 700,00 € | 500,00 € | 500,00 € |
i.e., if cell is blank it could show the "last sales" in the previous just period
If you can give me another idea, thanks...
🙂
That was tricky =D
This should work for you just create a new column:
var l = SUM(Sheet6[Sales])
var c = CALCULATE(SUM(Sheet6[Sales]),FILTER(ALLSELECTED(Sheet6[End].[Year]),Sheet6[End].[Year]-1))
return
if(l=BLANK(),c,l)
Regards
Abduvali
Hi @Abduvali,
I have tested it with steps you say in your response but I don't get the same results.
Mi new calculate column is:
lastyear =
var l = SUM(Ventas[ImporteVenta])
var c = CALCULATE(SUM(Ventas[ImporteVenta]);FILTER(ALLSELECTED(Ventas[Ejercicio]);Ventas[Ejercicio]-1))
return
if(l=BLANK();c;l)
Look the result
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 |
All cells shows the sum and for the cell of Customer5 and Customer7 with year 2017 these cells are empty.
it is possible that since there is no value in the table, it will not show anything in those cells
The original Sales table is:
NumVenta | Ejercicio | Cliente | ImporteVenta |
1 | 2016 | C1 | 1500 |
2 | 2017 | C1 | 500 |
3 | 2016 | C2 | 2000 |
4 | 2017 | C2 | 2000 |
5 | 2016 | C3 | 7500 |
6 | 2017 | C3 | 2500 |
7 | 2016 | C4 | 600 |
8 | 2017 | C4 | 600 |
9 | 2016 | C5 | 450 |
10 | 2017 | C6 | 2100 |
11 | 2016 | C6 | 1100 |
12 | 2016 | C7 | 2650 |
13 | 2016 | C8 | 9001 |
14 | 2017 | C8 | 4500 |
thanks
best regards
See my formula I have used date field there (my date field format is DD/MM/YYYY) and extended it to YEAR using DAX
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:
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
Hi @Abduvali
That is fantastic!! thank you, now it works, I was creating a calculated column instead of a measurement.
I have a new doubt, if I wanted to do it for the months, would it work the same way? , for example, if call ustomers all have sales in May but in November only some customer have it, and I want those that are blank in November to show me the May sales, how would it be in this case?
🙂
Hi @Abduvali
That is fantastic!! thank you, now it works, I was creating a calculated column instead of a measurement.
I have a new doubt, if I wanted to do it for the months, would it work the same way? , for example, if call ustomers all have sales in May but in November only some customer have it, and I want those that are blank in November to show me the May sales, how would it be in this case?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
141 | |
121 | |
112 | |
59 | |
58 |