Reply
New Contributor
Posts: 455
Registered: ‎08-25-2016

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

Hi @thne

Several observations on the fiel you sent:

 

1) The data prior to 1-Jul-2017 is all blank.  You may want to look at filtering that out in the query. Also, many, many rows have a blank Date value

 

2) When creating any visuals (while doing Time Intelligence functions), use the date field from your calendar, not from the fact table.

 

3) That being said, the Date field in your fact table (Blad1) has 2:00 AM for all values.  CALENDARAUTO() defaults to 12:00 AM for all dates, therefore even though you have created a relationship, there are no records that match between DateTimeTable and Blad1.  You have two options here - strip off the time value from Blad1 (in which case it will default to 12;00 AM) or create the calendar in a different manner (I would lean heavily towards the first option).

 

All of the above being said, I am frankly stumped as to why the code is not working.  I can get it working by creating a separate measure for the previous day value, and then a 3rd measure that is the difference between the today measure and previous day measure.  But I can't get the simple subtraction working inside a single measure with variables.  I will need to do some more digging.

 

Highlighted
New Contributor
Posts: 455
Registered: ‎08-25-2016

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

Hi @thne

 

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

Frequent Visitor
Posts: 13
Registered: ‎12-11-2018

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

Thank you so much for the time and effort you put into this, 

I will check this out!

 

Thanks and merry christmas! 

Frequent Visitor
Posts: 13
Registered: ‎12-11-2018

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

Hi again, for some reason, some of the value-series can't be selected. Eventough they are very similiar to the other values. I just get a blank screen

New Contributor
Posts: 455
Registered: ‎08-25-2016

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

Check that the data for the value names doesn't have white space or special characters.  The match needs to be exact with what you put in the SWITCH() statement.