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
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! :)