cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User I
Super User I

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

Hi @Anonymous - 

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.

 




Proud to be a Super User!




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

Highlighted
Anonymous
Not applicable

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! 

Highlighted
Anonymous
Not applicable

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

Highlighted
Super User I
Super User I

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.




Proud to be a Super User!




Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

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