Forum Discussion

bortxs's avatar
bortxs
Frequent Visitor
9 years ago

Table with two value-columns and their delta

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 l...
Need Help
v-chuncz-msft's avatar
v-chuncz-msft
Community Support
9 years ago

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's avatar
    bortxs
    Frequent Visitor
    9 years ago

    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's avatar
      bortxs
      Frequent Visitor
      9 years ago

      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... :smileyfrustrated:

       

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

       

      Borja

      • bortxs's avatar
        bortxs
        Frequent Visitor
        9 years ago

        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/m-p/135146/highlight/true#M57875). 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! :)