Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi folks
I've a question.
For example.
If the number is blank, return the last data that is no blank.
For example.
In October, I don't have the value, so, bring the last that isn't blank.
Best regards
Solved! Go to Solution.
Hello @igorabdo
I have normalized come columns and created the above model:
Then I have used the below measure to calculate the figures:
Avg Cost Last Non Blank =
SUMX (
Periods,
VAR curPer = Periods[DATA_INICIO]
RETURN
IF (
ISEMPTY ( RELATEDTABLE ( data ) ),
CALCULATE (
SUM ( data[CUSTO_MEDIO] ),
LASTNONBLANK (
CALCULATETABLE ( Periods, Periods[DATA_INICIO] <= curPer ),
COUNTROWS ( RELATEDTABLE ( data ) )
)
),
CALCULATE ( SUM ( data[CUSTO_MEDIO] ) )
)
)
Which is yielding the below results ( I have conditionally formatted in red the figures that are calculated using the LASTNONBLANK part )
I am not able to upload the file at the moment but if you still have doubts I will be able to upload it later in the day.
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
You can download it here: https://1drv.ms/u/s!AiiWkkwHZChHjxV0PpsCKUIBzJVu
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Check out the LASTNONBLANK formula: https://www.sqlbi.com/articles/semi-additive-measures-in-dax/
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hello @igorabdo
I have normalized come columns and created the above model:
Then I have used the below measure to calculate the figures:
Avg Cost Last Non Blank =
SUMX (
Periods,
VAR curPer = Periods[DATA_INICIO]
RETURN
IF (
ISEMPTY ( RELATEDTABLE ( data ) ),
CALCULATE (
SUM ( data[CUSTO_MEDIO] ),
LASTNONBLANK (
CALCULATETABLE ( Periods, Periods[DATA_INICIO] <= curPer ),
COUNTROWS ( RELATEDTABLE ( data ) )
)
),
CALCULATE ( SUM ( data[CUSTO_MEDIO] ) )
)
)
Which is yielding the below results ( I have conditionally formatted in red the figures that are calculated using the LASTNONBLANK part )
I am not able to upload the file at the moment but if you still have doubts I will be able to upload it later in the day.
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi LivioLanzo, thanks very much for your solution, it works great for me on the lowest level of data (PK_ITEM in your example) - but when I remove the items from a table visual and want to have a total per month then the numbers don't add up properly, would you know how to fix it?
Thanks.
Please, share you pbix.
Best regards.
You can download it here: https://1drv.ms/u/s!AiiWkkwHZChHjxV0PpsCKUIBzJVu
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |