Create Table from SUM of values from other tables

Question

I have created measures called prev1, prev2, cur1, cur2 which are sum of numbers. Then I created a table called using "Enter Data" button. In the first column I have entered Item_A, Item_B

I am trying to acheive the below:

Col1 PREV CUR
------ ---- -----
Item_A Prev1 Cur1
Item_B Prev2 Cur2

E.g.:

Col1 PREV CUR
------ ---- -----
Item_A 123 312
Item_B 213 132

I tried the below but it gave me a very large number.

PREV = SWITCH([Col1], Item_A", [Prev1])

Also tried this but its the same large number even when I try with CALCULATE

PREV = sumx(FooBar, [Prev1])

This may be becasue I have slicers and it ignores the filter context from those slicers. So I have tried ALLSELECTED() but it does not change the figures on a measure:

CALCULATE(sum(Cur1), ALLSELECTED())

Please can someone enlighten me.

EDIT 1:

I have just rechecked my data and the problem is as I am suspected that it does not apply the FILTER CONTEXT from the report.

EDIT 2: I have manged to get the figure that I am after using the below but its hard coded: calculate(SUM(Cur), Date = date(2019,4,22))

When I then use the below code to automate that I see no figure at all: calculate(SUM(Cur), Date = SELECTEDVALUE(date, ""))

So now I think if I can convert the returning value {SELECTEDVALUE(date, "")} to date that might work but how should I do that?

Remove the alternate result (" ") from selectedvalue, it is converting the output to type text — Pratik Bhavsar
Thanks @PratikBhavsar but that didn't work too but I have just figured it out and posted the answer. — Unbound

Unbound Unbound · Accepted Answer · 2019-05-15T12:16:26

I have finally got the this:

calculate(
    SUM(cur), 
    filter(
        myTable, 
        dateID = related(dateID)
    )
)

So, I am making sure that the value is calculated by complying to the relationship between the two tables. This way I am making sure that FILTER CONTEXT is intact.

Create Table from SUM of values from other tables

2 Answers