Automatically recalculate the index as soon as the month changes

DAX DAX Calculations
1

Hello team,

I hope you are well,
I wish on PowerBI when all the months are selected to have the same values ​​for Index, Cross Multiplication -1 and Cross Multiplication -2
(see image below).

samevalues
samevalues791×156 17.9 KB

Then as soon as I go to the month of April for example Cross multiplication -2 recalculates the index to base 100 from the month of April and so on.
(see image below)

index cross2
index cross2769×258 20.2 KB

Cross Multiplication -2 works fine if I select one (image 1), but doesn’t work when all items are displayed (see image below).

ko_index
ko_index694×374 40.4 KB

I wish that once I select a new month in the slicer, the base month changes to this month with
the right clues for Cross Multiplication -2.

I tried several methods without success, I searched in the forum also without success.

attached the example powerbi
Question.pbix (73.3 KB)

Thank you very much for your help.

2

Hi @tapzad ,

Thanks for posting your question in detail and attaching sample PBIX. I think you are looking for below solution?

image
image1203×565 37.1 KB

The reason you were getting wrong results for cross multiplication was that you were removing all filters from Base table. In this way, you were removing filter from elements column as well, so it was giving you wrong result. I just made a small tweak in the formula and it is working fine. Please find below formula and attached PBIX.

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(  SUM( Base[values] )  , 
    FILTER( ALLEXCEPT( Base ,Base[elements]) , 
        Base[month] = _Min_Date ) )

VAR _Results = 
DIVIDE( 
    _Selected_Value * 100 , 
    _First_Occurence_Value_For_Each_Category , 
    0 )



RETURN
_Results

Question cross multiplication.pbix (71.2 KB)

3

Hello @hafizsultan,

Thank you very much for your help, I have tested but it does not work when I apply it to my data. I will try to share examples again.

I will mark as solved and I will open a new topic as soon as I have the right examples.

Thanks

4

Hi @tapzad ,

Thanks for testing this solution. You can keep it open and provide some example where it is not working and I can provide some tweak.

Kind Regards,
Hafiz

1 Like