Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
69 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
68 |