MAX/MIN - Measure based on time period slicer, ideas?

[Anonymous]

Hi everyone,

My ambition is to write a measure that calculates the difference between two selected time periods using a filter slicer.

After some googling around, I found a solution that is ALMOST working. The solution is using MAX and MIN formulas in variables

to calculate difference between the max and min value selected. https://www.fourmoo.com/2019/05/08/dax-calculating-the-difference-between-2-selections-on-a-slicer/ 

Based on the solution I was given, I created the following:

FIN_Periodic_TimeDifference =

VAR SelectedTime =

VALUES(Time_Lookup[Period])

VAR MinValue =

MINX(SelectedTime;CALCULATE([FIN_Periodic]))

VAR MaxValue =

MAXX(SelectedTime;CALCULATE([FIN_Periodic]))

VAR Difference = MaxValue - MinValue

VAR NumPeriodsSelected =

COUNTROWS(SelectedTime)

VAR Result =

IF (

NumPeriodsSelected = 2;

Difference;

ERROR("Please select only two periods")

)

RETURN

Result

First, I thought I had made it to work. But then I realized that the measure is always calculating the absolute difference, not paying attention to the order of the time periods.

In other words, I want the measure to always take the MAX value to always represent the latest time period selected and the MIN value to always represent the time period most long ago selected.

In the example I have attached a picture where you can see the error. I get a positive number when I should get a negative number.

Here is the link to my screenshots: https://drive.google.com/drive/folders/1FbNNMIYHnagfuaLxRez3cEXBMZC8NRrR?usp=sharing

Does anyone have any idea how I can re-write the measure? I was thinking of using some kind of RANK formula, but then again I am not so good with DAX.

Any help greatly appreciated 😃 

/Rasmus 

[Greg_Deckler]

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

Sample data as text.

[amitchandak]

When you take diff in measure, you need to take care of row context. Please refer to this blog: https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

True for import. See how order number is used as context for difference

[Anonymous]

Thanks for replying. When you see me measure,how would you re-write the DAX? I am not sure I am following your example. 

[Greg_Deckler]

@Anonymous, can you post sample/example data as text?

[Anonymous]

Certainly. First of all, did you see the two pictures I posted in the link?

There are only few columns involved in this issue.

There are two tables related to this issue in my dataset: "FIN_LogUnits Data" (data table) and "Time_Lookup" (Own constructed calander table).

In "FIN_LogUnits Data" the two columns related to this:

"Fin_Log_Period" (data column with values) and "TIME" which is the key-column that goes to "Time_Lookup".

In "Time_Lookup", except for "TIME" there is "Period" which I am filtering on in this case. 

Thanks a lot for showing an interest to help! 🙂 

[v-chuncz-msft · Accepted solution]

@Anonymous 

You may refer to the post below.

https://community.powerbi.com/t5/Desktop/measure-between-two-dates-Modified-date-file/m-p/569889#M269029

[Anonymous]

This methodology worked! 

Thanks a lot!