I am new and I thank you all for your various contributions.
After having searched well, I still cannot find the solution to my problem, so I allow myself to share it with you hoping to find a solution.
I am faced with a problem on power bi, I want to reproduce an excel operation, which consists of calculating for each element of my first column an index based on 100 with respect to a reference date (start date) and a corresponding value.
As you see the formula in the image below, for the start date and the corresponding value we get 100, for the following lines for the same element the calculation is based on the 2 fields of the first line, once we have finished calculating the indices for the first element we must resume the calculation for the second element exactly like the first element and so on for all the elements of the first column.
I added an excel file and a pbi to illustrate the example.
I want to obtain in Power BI the same result as the index column
To be able to choose a reference date (departure date), once the reference date has been chosen via a filter, everything is recalculated according to this date
So do you mean that once a new month in selected into the slicer the base month should shift to that particular month? In that case, here’s the another measure alongwith the screenshot of the final results provided for the reference -
Cross Multiplication - 2 =
VAR _Selected_Value =
SUM( Base[values] )
VAR _Min_Date =
CALCULATE( MIN( Base[month] ) ,
ALLSELECTED( Base ) )
VAR _First_Occurence_Value_For_Each_Category =
CALCULATE( MIN( Base[values] ) ,
FILTER( ALLSELECTED( Base ) ,
Base[month] = _Min_Date ) )
VAR _Results =
DIVIDE(
_Selected_Value * 100 ,
_First_Occurence_Value_For_Each_Category ,
0 )
RETURN
_Results
In the above screenshot, you’ll observe that first calculation considers the March month as the Base month but the new measure considers now, May month as the Base month rather than considering the month of March and it’ll continue to shift or change the Base month based on the first month selected in the slicer.
I’m also attaching the working of the PBIX file for the reference purposes.
Hoping you find this useful and this meets your requirements that you’ve been looking for.
In reality it is not a new subject, it is a subject that I had marked as solved but by applying it I see
that it does not work in all cases. In such a case do I have to open a new topic ?
The reason people are advised to open a new topic is that those are more likely to be seen by the community - not everyone goes into the threads that have multiple replies, unless they are researching a similar problem.
Posting a new topic will bring your problem to the community more quickly, and you can always reference back to the original post with a link if you feel that something in that post is relevant to the new/modified problem.
I suggest including a copy of your PBIX as it stands (possibly with the solution that is not fully working), and an explanation of the new parameters and why the solution isn’t working, even an example of the expected results in the new post.
