Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello, I am trying to find out how to get the latest values. Dummy data:
Name Date Value 1 value2 value 3
sd 12/10-2017 1 0 1
tx 12/10-2017 0 1 0
sd 13/12-2017 1 1 0
I would like to be able to diplay the latest data for each "name", how to do this? I need a simple way to this, because I have 53 "values". The ideal solution for me, would be to generate a table with only the latest values. So it automatically updates, and I am able to use the information as fit.
Solved! Go to Solution.
NOw you can use this MEASURE
Value at Recent Date = CALCULATE ( SUM ( TableName[Value] ), FILTER ( TableName, TableName[Date] = MAX ( TableName[Date] ) ) )
One of the ways of doing it is to create a Calculated Table
Go to Modelling Tab >>> NEW TABLE
New Table = SUMMARIZE ( TableName, TableName[Name], "Date", MAX ( TableName[Date] ), "Value 1", CALCULATE ( SUM ( TableName[Value 1] ), FILTER ( TableName, TableName[Date] = MAX ( TableName[Date] ) ) ), "Value 2", CALCULATE ( SUM ( TableName[Value 2] ), FILTER ( TableName, TableName[Date] = MAX ( TableName[Date] ) ) ), "Value 3", CALCULATE ( SUM ( TableName[Value 3] ), FILTER ( TableName, TableName[Date] = MAX ( TableName[Date] ) ) ) )
Any easier way? I have 53 "values"
In that case, unpivot the 53 columns using QUERY EDITOR
You will get
NOw you can use this MEASURE
Value at Recent Date = CALCULATE ( SUM ( TableName[Value] ), FILTER ( TableName, TableName[Date] = MAX ( TableName[Date] ) ) )
This works on one of my unpivoted columns thank you!
It works on the column containing only 1 and 0 data. However I have another column,
where I want to do the same, however this column contains data that exceeds 1 and 0.
How can I make that work?
@BiBraSorry, I'm not sure if you are referring to my suggestion. If so, can you explain further what the problem is? the function CLOSINGBALANCEMONTH or YEAR returns the last (date) value in a column. In other words, the field "value" must be in the same column in a table.
Proud to be a Super User!
Paul on Linkedin.
@PaulDBrown I am not referring to your solution. I am referring to @Zubair_Muhammad. When using his method. it works when the unpivoted column only contains 1 and 0, not when values higher.
@BiBraOK, sorry...
Proud to be a Super User!
Paul on Linkedin.
Is this only for one value?
No you will get all 53 values in one shot
Try it
I have now tried it, however I trying to find out if I am able to use this measurein charts?
Not sure if this will work, but it might be worth a try:
last value = CLOSINGBALANCEMONTH([value], date[date])
EDIT: If the preriod you are looking at is a whole year, use CLOSINGBALANCEYEAR instead.
Proud to be a Super User!
Paul on Linkedin.
Use can then use a MATRIX VISUAL
Put
1) Name is Rows
2)Attribute in Columns
3) Value at Recent Date in Values
User | Count |
---|---|
94 | |
85 | |
78 | |
68 | |
63 |
User | Count |
---|---|
113 | |
99 | |
97 | |
64 | |
59 |