Calculate movement of cost based on month and Month Slicer

[Anonymous]

I have a table visual based on Month and Cost and it has a Month slicer as shown below:

I want to calculate cost movement but the ask is that when the relative slicer is set then the minimum date should have a base cost of £1.27 of Month 2021-01. This base cost is use to divide all other month to Maximum month of 2021-08. 
For example, the movement from 2021-01 to 2021-02 is £1.25 divided by £1.27 = 99% and another example is movement from 2021-01 to 2021-02 will be £1.25 divided by £1.27 = 99% and for movement 2021-01 to 2021-03 is £1.27 divided by £1.27 = 100% and so on till movement from 2021-01 to 2021-08 will be £1.26 divided by £1.27 = 99.2%. How can I calculate this logic in Power BI (dax).

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[hnguy71 · Accepted solution]

You can try something like this:

Movement = 
-- Get min date in context
VAR _minDate = CALCULATE(MIN('Table'[Date]), ALLSELECTED('Table'[Date]))
-- Get current date value in context
VAR _maxDate = MAX('Table'[Date])

-- Calculate for min date value
VAR _minVal = CALCULATE(SUM('Table'[Cost]), 'Table'[Date] = _minDate)
-- Calculate for values within range.
VAR _currVal = CALCULATE(SUM('Table'[Cost]), KEEPFILTERS('Table'[Date] >= _minDate && 'Table'[Date] <= _maxDate))

RETURN

-- Divide current value against minumum value for movement percentage. 
DIVIDE(_currVal, _minVal)

[Anonymous]

Thanks @hnguy71 it worked perfectly. Your dax is on point👏

Please, I posted a problem a week ago, it involves dax. The link below:

https://community.powerbi.com/t5/Desktop/How-to-use-DAX-expression-to-calculate-count-members-based-...

[hnguy71]

Hey @Anonymous ,

The answer to that is similar to this.

1. Break your relationship between relative period and the date table.

2. Harvest the minimum relative date and the maximum relative date. 

3. Generate your new min and max dates

4. Count the values between those dates similar to your "movement".

[Anonymous]

To understand this logic, I have the screenshot below in excel. Movement is calculate by Column E .
Hence, when i select my relative month Slicer from 2021-01 to 2021-08 then minimum cost base from month 2021-01is 1.27 that is used to divide other months to get movement in % or decimals.

Hope it make sense.

[Anonymous]

I am unable to understand the requirement. If you can exolain a bit in detail and maybe an example in excel with what the cost movement column might look like.