Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 ...
Need Help
Tutorial Requests
  • dedelman_clng's avatar
    dedelman_clng
    7 years ago

    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

dedelman_clng's avatar
dedelman_clng
Community Champion
7 years ago

Yes, it has a little to do with the data structure.  You will need to create another table with a single column and the values that you want shown in your slicer (often "Enter Data" is the way to do this).  Once you have that table, create MType against that table.

 

MType = SELECTEDVALUE(SaleType[Type])

TotAmt =
SWITCH (
    [MType],
    "Customer", SUM ( SalesTab[Customer] ),
    "Invoice", SUM ( SalesTab[Invoice] ),
    "Sale Amount", SUM ( SalesTab[Sale Amount] )
)

 

Hope this makes sense.

David

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thank you so much for the help! 

 

The Slicer is working now! 

 

When I try to do the comparison that was mentioned earlier I get an error for this part:

Measure Delta = var __SelectedMeasure = 
     SWITCH (
            [MType];
            "bla bla"; AVERAGE(Table[Value]);
             etc etc
and then the error here (underlined): 
var __PDMeasure = CALCULATE((__SelectedMeasure; PREVIOUSDAY(Blad1[Tid]))

RETURN __SelectedMeasure - __PDMeasure;

This Measure is made inside the table containing the values and time.

The MType measure is inside the second table 

MType = SELECTEDVALUE(SelectValue[ValueSelect])

The syntax for 'RETURN' is incorrect. (DAX(var __SelectedMeasure = SWITCH( [MType], "BJ-HÖ1 etc etc...