Current Month vs previous month combining with If

[AlbertLInd]

Dears,

thank you in advance for helping.

I have a table with Expenses in different periods. I want to table or a matrix, that shows me the expenses in each period. Furthermore I need a column that shows me the current month vs previous month. The Date or Period should be selected in a slicer. In addition to that, I want a slicer or button, that shows me only the changes from current month and previous month, which are higher than 30 %.

I have so far a date table and the mesaures to calculate the the current and previous month, but I dont know how to show only the changes, which are higher than 30 %. My idea was to create a calculated column, but I cant use the measures in the formulas.

These are my Formula:

Expenses for this Period = sum('Aufwandsbuchungen SAP Donwload'[Betr. in HW])

Expenses Previous Period = Calculate(sum('Aufwandsbuchungen SAP Donwload'[Betr. in HW]),DATEADD('Date Table'[Date],-1,month))

or with dynamic Dates.

Previous Period Expenses Between =

CALCULATE(sum('Aufwandsbuchungen SAP Donwload'[Betr. in HW]),

DATESBETWEEN(

'Date Table'[Date],

[Start of Previous Period],

[End of Previous Period]

)

)

Thank you and best regards,

Albert

[SpartaBI]

@AlbertLInd just wrap the result of the %change measure with another step. Somthing like:

MEASURE= 
....
VAR _prveious_result = ...
VAR _result = IF(_prveious_result > 0.3,_prveious_result)
RETURN
       _result

This will blank out any result that is less than 30%
In case it answered your question please mark this as a solution for community visibility. Appreciate Your Kudos 🙂

[AlbertLInd]

Hi @SpartaBI,

thank you for responding.

I would like to see the big changes (more than 30 %) only by clicking on a slicer. It should be only a possibility to see the big changes.  Is that possible?

With a calculated column I could use it in a slicer, but it does not work with the measures.

Thank you and best regards,

Albert

[SpartaBI]

@AlbertLInd sure.
Create a disconnected table with one column and let's say these values: "Big Changes", "All"
Than put that columm as a slicer and make it single select.
Add to your measure this logic:

MEASURE= 
....
VAR _prveious_result = ...
VAR _big_changes = 
     IF(_prveious_result > 0.3,_prveious_result) 
VAR _result = 
     IF( SELECTEDVALUE('Table'[Column]) = "Big Changes", _big_chhanges, _previous_result)
RETURN
       _result

In case it answered your question please mark this as a solution for community visibility. Appreciate Your Kudos.

[AlbertLInd]

@SpartaBI Can you please help me with the measure? Something does not work. I am new to power bi.

Measure = 
VAR previous_result = [Expenses Previous Period]
Var big_changes = 
    if(previous_result > 0.3,previous_result)
Var result =
    if(SELECTEDVALUE(Slicer_Changes[Slicer_Changes] = "Big Changes"), big_changes, previous_result)
    Return
        result

[SpartaBI]

@AlbertLInd had a typo with brackets. I fixed it. Take the code now from the previous message

[AlbertLInd]

@SpartaBI 

Ahh I see.  The measure is okay now, but if I select the slicer "Big Changes" its nothing happening. 

I created a new table with one column with the entries "All" and "Big Changes". Thats it right?

In the measure the following lines are not clear for me:

Var big_changes = 
    if(previous_result > 0.3,previous_result)

It think it means, if previous result is bigger than 0.3 than show me previous result? But I need the Variance between actual and previous month. Could this be the problem in the function?

[SpartaBI]

@AlbertLInd I Assumed you already did that so the previous result mean that variance

[AlbertLInd]

 Ahh Okay, but i still confused. I dont understand which value (measure) I should use for VAR_previous result. Currently I am using the Expenses previous Period. Thats not working but with the variance its also not working. 

I have these measures:

Variance between actual and previous month:
Variance Period Month = [Expenses for this Period]-[Expenses Previous Period]

Variance in %: = ABS(Divide([Expenses for this Period]-[Expenses Previous Period],[Expenses for this Period]))

Expenses previous period ( What is currently my VAR)Expenses Previous Period = Calculate(sum('Aufwandsbuchungen SAP Donwload'[Betr. in HW]),DATEADD('Date Table'[Date],-1,month)) 

I do not know, what I should use for VAR_previous_result.

Sorry for asking so many questions.

@SpartaBI

[SpartaBI · Accepted solution]

MEASURE = 
VAR _change = [Variance in %]
VAR _big_changes = 
     IF(_change > 0.3,_change) 
VAR _result = 
     IF( SELECTEDVALUE('Table'[Column]) = "Big Changes", _big_changes, _change)
RETURN
       _result

 @AlbertLInd no worries. I wrote to you the complete measure that uses your current measure.

In case it answered your question please mark this as a solution for community visibility. Appreciate Your Kudos!

[AlbertLInd]

@SpartaBI Thank you a lot, it works now.

I have one last question.

Now when I filter only significant changes (>0.9), it is only filtering for the meausre you helped me. When I have  a table with another column it shows than blanks. I dont want to see the blanks. Is it possible?

The red marked rows should disappear.

Thanks a lot again 🙂

[SpartaBI]

2 options:
1) you can go the visual level filters and restrict there that this measure will not be blank. It will remove the rows entirely.
2) You can add that restriction to each of the measures in your visual, so all of them will blank.

The first option is of course more easy, but sometimes it less performant. 

In case it answered your question please mark also this as a solution for community visibility. Appreciate Your Kudos 🙂