Forum Discussion
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
Community ChampionFor 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
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])
Thanks
- dedelman_clng
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
Thank you for the help, I'll try your solution when I'm working on this next.
Best regards
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.
Thank you again
- dedelman_clng
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