Table with two value-columns and their delta

[bortxs]

Hello guys, 

I´m trying to create a table showing information of one week and previous week (it can also be month, this is not relevant) together with its delta. The idea is to create something like this:

	Calendar Week 30	Calendar Week 31	Δ%
Net Order Volume	15.000	16.500	10%
Shipping	1.000	800	-20%
Total Net Order Volume	16.000	17.300	8%

Unfortunately I only get to have the weeks on the left side as rows (where the KPIs are), but not as columns, so that under each week I can show not only Net Order Volume but also number of orders, number of items ordered, or some other KPIs. Any idea?

Thanks a lot for your help!

Best regards,

Borja

[v-chuncz-msft]

@bortxs,

You may refer to the following DAX that creates a new table.

Table =
ADDCOLUMNS (
    GROUPBY (
        Table1,
        Table1[KPI],
        "Calendar Week 30", SUMX ( CURRENTGROUP (), IF ( Table1[Calendar Week] = 30, Table1[Volume], 0 ) ),
        "Calendar Week 31", SUMX ( CURRENTGROUP (), IF ( Table1[Calendar Week] = 31, Table1[Volume], 0 ) )
    ),
    "Δ%", DIVIDE ( [Calendar Week 31] - [Calendar Week 30], [Calendar Week 30] )
)

[bortxs]

Thanks @v-chuncz-msft! 

I think I didn´t express myself correctly... I said I wanted a table (indeed, that´s what I want...) but I suppose the correct name for that would be a "Table-Visual"... If I understood correctly, your solution would create me a new table, right? 

I have created this (see below) for a similar purpose using  3 card-visuals and a rectangle... the idea would be to create something similar but not so "rudimentary" (if possible), so that I can have few different KPIs under each "column" (be it YTD vs PYTD, be it CW31 vs CW30...)

Sorry for the confusion! and thanks a lot again!

Borja

[bortxs]

Hello guys, 

regarding this topic I thought I could do something like this...

I´ve created a Slicer with the Calendar Weeks (CW) and besides that I´ve created a column in the Dates-Table that gives me automatically the week before (very simple: when I select the CW25, it gives me the CW24 etc.). 

CW Slicer

My first solution it´s been to create two slicers and "unlock" each "Multirow Card" from the week I don´t want it to be influenced by... but I find it a bit too messy. 

Small table above with the CW and PCW fields, below the needed KPIs in 2 "Multirow cards"

In order to optimize it a bit, I would like to create a measure which reacts not to the week selected on the slicer (I want to have only one) but to the value corresponding to "the week selected on the Slicer -1 (minus one)". 

I have created this formula: "Net Order Value PW = CALCULATE(SUM('DATAVALUES'[Net Order Value]);CW[PCW])" but unfortunately it´s not working properly... 

Would you guys have an idea of how the DAX function should look like in this case? Thanks!!

Borja

[bortxs]

I´ve tried with the solution from @v-ljerr-msft from this thread: (https://community.powerbi.com/t5/Desktop/Cards-to-show-previous-period-and-or-exclude-current-period...). I adapted the code and got this:

Net Order Value PW = 
VAR currentWeek = MAX(DATUM[Week]) 
VAR currentYear = MAX(DATUM[Year]) 
RETURN (IF(currentWeek=1;CALCULATE([Net Order Value];FILTER(ALL(DATUM);DATUM[Year]=currentYear-1 && DATUM[Week] = 52)); CALCULATE([Net Order Value];FILTER(ALL(DATUM);DATUM[Year]=currentYear
&& DATUM[Week] = currentWeek-1))))

...but unfortunately it´s not working either... 

Thanks for your comments/help! 🙂