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 all,
I am trying to migrate one of the report from tableau to power bi.
We have a table like below:
Sample table:
Name Date Value
A 4/19 10
A 4/21 20
B 4/20 10
And we want to create a table in power bi which can inherit the previous date value.
Like this one:
Name Date Value
A 4/19 10
A 4/20 10
A 4/21 20
B 4/19 null
B 4/20 10
B 4/21 10
The challenge here is we don't have A for Date 4/20 or B for Date 4/19 or 4/21 in the database.
In tableau we have previous_value expression to loop through all the date without a context filter so I want to ask if there is an option in power bi which can give me the result above without adding date and value in the original database.
THX
Solved! Go to Solution.
Please see attached file
Hi @rawmeat
try this calculated table
From Modelling Tab>>NEW TABLE
New Table = VAR AllCombinations = CROSSJOIN ( VALUES ( Table1[Date] ), VALUES ( Table1[Name] ) ) RETURN ADDCOLUMNS ( AllCombinations, "Value", VAR mydate = CALCULATE ( MAX ( Table1[Date] ), FILTER ( table1, Table1[Name] = EARLIER ( [Name] ) && Table1[Date] <= EARLIER ( [Date] ) ) ) RETURN CALCULATE ( SUM ( Table1[Value] ), FILTER ( Table1, Table1[Name] = EARLIER ( [Name] ) && Table1[Date] = mydate ) ) )
Thanks much it works perfect.
Ok, I got it, I use summerize to create a new table.
Test = SUMMARIZE(Table1,Table1[Name],Table1[Date],"Value",SUM(Table1[Value]))
Thanks again Muhammed
Please see attached file
Sorry to bother again, just one more question what if I have multiple value for one day and want to aggregate.
For example
Name Date Value
A 4/19 10
A 4/19 10
A 4/21 30
B 4/20 10
To be
Name Date Value
A 4/19 20
A 4/20 20
A 4/21 30
B 4/19
B 4/20 10
B 4/21 10
Really appreciate your help
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |