Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a data source similar to the example, with different indicators, different dates and the value of the indicador for each date.
I want to get the last value of the year, when the value is not 0.
If I use "calculate(lastdate([date]),not([value])=0)", I get "30/06/2020". But, when I try it with "lastnonblank" I get 43,50 for the Id 0.2 instead of 40,00.
The goal is show in a table the last value for the year (according to dates) when it is not 0, and If I filter for each quarter obtein the value for this date. In this case, I want to get for the year 2020 the value 79,80 for Id 0.1 and 40,00 for Id 0.2.
Could you help me to solve this dax expression?
Thanks,
Solved! Go to Solution.
Hi @jacob2102
LASTNONBLANK Returns the last value in the column, column, filtered by the current context, where the expression is not blank. Because your date column is not arranged from smallest to largest, you get unexpected results.
After I sorted the date column, I used the following measure to get the expected result:
Measure = LASTNONBLANKVALUE('Table'[Date],SUM('Table'[Value]))
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @jacob2102
LASTNONBLANK Returns the last value in the column, column, filtered by the current context, where the expression is not blank. Because your date column is not arranged from smallest to largest, you get unexpected results.
After I sorted the date column, I used the following measure to get the expected result:
Measure = LASTNONBLANKVALUE('Table'[Date],SUM('Table'[Value]))
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Thanks for your answer.
One of the problemas was in the data order. I don't know why in although I orden the data in PowerQuery, this order not appear later in the table.
To solve the problem, I have ordered the dates, identify the values "0" like blanks and use your measure. With this changes, now I obtain the correct resoult.
Thanks.
@jacob2102 , Try like
LASTNONBLANKVALUE(Table[fecha], max(Table[valor]))
sumx(summarize(Table, Table[Id],"_1",LASTNONBLANKVALUE(Table[fecha], max(Table[valor]))),[_1])
Hi,
First of all, thanks for your answer, but this option is not the solution.
In this case I have all dates for the year, some of them with value and others with 0. If I use this dax expression I get 0 like a resoult, becasuse the last date is 31/12/2020, and at this moment I don't have value for this date.
In this case, I would like to obtain the value for the las date that not is 0, in the example 40,00 for ID 0.2 in the date 30/06/2020.
Thanks,
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |