cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Current vs Previous value, not dependent on base value

Hi,

 

I'm wondering if there's a way for me to have a chart displaying one value over time, with a bar below showing the difference between the current and previous value, BUT i want to be able to change the base value between different values?

 

Let's say I have 15 different sets of values that I want to present this way, so instead of me making 15 different calculated measures, one for each value, I wonder if there's some sort of way for me to make a column/measure or something that does what I'm after? And then I can just click and change between the values that I want.

 

The only constant are the time values. 

 

Hope this made any sense and thanks for any help.

 

To clarify: Ability to switch between different values to present both line for values and bars for increase or decrease of the value. 

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: Current vs Previous value, not dependent on base value

Hi @Anonymous

 

I gave you some erroneous code previously (I hadn't tested it - i was making extrapolations from other similar work I've done, but I had some false assumptions).

 

Points 1-3 in the above reply still hold - please review them.  However, now we need to modify the measures.

 

For transparency, we'll use 3 measures. I'll talk about where they can be combined afterwards

 

TodaysValue= 
    SWITCH(
        [MType];
        "BJ-HÖ1"; AVERAGE(Blad1[BJ-HÖ1]);
        "BJ-MÄ"; AVERAGE(Blad1[BJ-MÄ]);
        "BJ-TS1"; AVERAGE(Blad1[BJ-TS1]);
        etc
    )

PrevDayValue = CALCULATE([TodaysValue]; PREVIOUSDAY(DateTimeTab[Date]))

Measure Delta = [TodaysValue] - [PrevDayValue]

The mistake was thinking we could store some of the complex code in variables and use the code again further down in the measure.  I think we can really only have a minimum of 2 measures, unless you want to replicate a lot of code:

 

TodaysValue= 
    SWITCH(
        [MType];
        "BJ-HÖ1"; AVERAGE(Blad1[BJ-HÖ1]);
        "BJ-MÄ"; AVERAGE(Blad1[BJ-MÄ]);
        "BJ-TS1"; AVERAGE(Blad1[BJ-TS1]);
        etc
    )

Measure Delta = 
//NOTE That variables are not needed here - just showing for illustrative purposes VAR __Todaysvalue = [TodaysValue]
VAR __PrevValue = CALCULATE([TodaysValue], PREVIOUSDAY(DateTimeTab[Date]) RETURN __Todaysvalue - __PrevValue

//You could do the following without any variables and get the same results
//Measure Delta = [TodaysValue] - CALCULATE([TodaysValue], PREVIOUSDAY(DateTimeTab[Date])

Hope this finally gets you to where you need to be.  Sorry for the earlier confusion.

 

David




Proud to be a Super User!




View solution in original post

24 REPLIES 24
Highlighted
Super User I
Super User I

Re: Current vs Previous value, not dependent on base value

If I understand your issue correctly, try this pattern:

 

Slicer/button to choose your value

MType = SELECTEDVALUE(Slicer[Field])

Measure for bar or line

Bar/Line Value = 
SWITCH (
    [MType],
    "Value1", [Measure 1],
    "Value2", [Measure 2],
...
)

Hope this helps

David




Proud to be a Super User!




Highlighted
Anonymous
Not applicable

Re: Current vs Previous value, not dependent on base value

Spoiler

@dedelman_clng wrote:

If I understand your issue correctly, try this pattern:

 

Slicer/button to choose your value

MType = SELECTEDVALUE(Slicer[Field])

Measure for bar or line

Bar/Line Value = 
SWITCH (
    [MType],
    "Value1", [Measure 1],
    "Value2", [Measure 2],
...
)

Hope this helps

David


Thank you, that will for sure help me with one part of my problem, but I'm still unsure as how to get the value difference working? 

Highlighted
Super User I
Super User I

Re: Current vs Previous value, not dependent on base value

You can make [Measure 1], [Measure 2], etc as complex as you want to or need to.  Getting a "previous" value is going to depend on what you mean by previous.  Can you give some sample data and expected results?

 

Also, see this post on how to get the most out of the forums and get your question answered quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490




Proud to be a Super User!




Highlighted
Anonymous
Not applicable

Re: Current vs Previous value, not dependent on base value

Thank you for the reply,

Here's a screen grab of a table displaying en example of the values. What I would like to do is have the value of june 23 - value of june 22, value of june 22 - value of june 21 etc etc.  This is so that I can hopefully make it so that if the difference between two dates is more than x that bar should be red or something. 

 

The second image is an example of what I mean. 

 

Thank you so much for the help, I'm still pretty new to PowerBI

 

scrnshot1.PNGtjosanhoppasn.PNG

Highlighted
Super User I
Super User I

Re: Current vs Previous value, not dependent on base value

For the daily difference, you'll want something along these lines

 

Previous Day HE-TS = 
CALCULATE ( [Average of HE-TS], PREVIOUSDAY(DateTab[Date])

Day Delta = [Average of HE-TS] - [Previous Day HE-TS]

DateTab[Date] should be the name of your calendar/date table. 

 

Hope this helps

David




Proud to be a Super User!




Highlighted
Anonymous
Not applicable

Re: Current vs Previous value, not dependent on base value

Thank you,

 

But is there any way to make it not dependen on the value? If that makes sense. 

So that I can change between the different value sets without changing or making new of these codes for every value? I typed and example of what I mean, don't know if there's any way to make it like that... hope you understand how I mean.

Previous Day HE-TS = 
  CALCULATE ( [Average of HE-TS], PREVIOUSDAY(DateTab[Date])

Previous Day BJ-TS1 = 
  CALCULATE ( [Average of BJ-TS1], PREVIOUSDAY(DateTab[Date])

Example:
CALCULATE ( [Average of SELECTEDVALUE], PREVIOUSDAY(DateTab[Date])

 

 kjhjkkhj.PNG

 

Thanks 

Highlighted
Super User I
Super User I

Re: Current vs Previous value, not dependent on base value

Combining my first answer with my second answer, you would want to do something like this:

 

Slicer/button to choose your value

MType = SELECTEDVALUE(Slicer[Field])

Delta based on selected type

Measure Delta = 
var __SelectedMeasure = SWITCH ( [MType], "Value1", [Measure 1], "Value2", [Measure 2], ...)

var __PDMeasure = CALCULATE(__SelectedMeasure, PREVIOUSDAY(DateTab[Date))

RETURN __SelectedMeasure - __PDMeasure

 

Hope this helps

David




Proud to be a Super User!




Highlighted
Anonymous
Not applicable

Re: Current vs Previous value, not dependent on base value

Thank you for the help, I'll try your solution when I'm working on this next.

 

Best regards 

Highlighted
Anonymous
Not applicable

Re: Current vs Previous value, not dependent on base value

Hi,

 

So I'm having some troubles with this, when I try to create the first measue (MType = etc) I can't add this to my slicer, and I also seem to not be able to add all the values I want to the slicer, I can only add one of them. So instead of me having the 15 values in a list I can only have one and filter by it's values. Instead of only filtering based on the set of values, if that makes sense. 

 

Could this have something to do with the structure of the data? It's all imported from one excel table. 

blkjlkjg.PNG

Thank you again

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors