Request for Assistance with Power BI DAX Logic

[bchappidi86]

Hi Everyone,

I’m currently working on a Power BI requirement where I need help creating a DAX calculation.

The goal is to create a dynamic rank that resets based on matching effective dates from a reference table. Here's the logic:

• The first block (starting from the minimum date based on "End date" slicer from disconneted table, "period" slicer) should have the rank sequence: 0, 1, 2, 3, etc.

• When a match with an effective date (from the Ref Data table) is found, the rank should reset and start again at 1, 2, 3, etc.

• Also, I would like to carry over the previous last value to use in calculations for the new block.

Ref Data table- Example:

Expected Output:

If you see below output, first block of the rank started from 0, 1,2,3,4 etc and then 

The rank resets on 31-10-2021 because a new tracking error appears in the Ref Data.
The new tracking error is multiplied by (Rank + previous last value) for the calculation

I've created calculated columns to:

• Identify the blocks

• Generate ranks within each block

• Reset the ranks based on matching effective dates

However, I’m now facing challenges where I need the ranks to dynamically adjust based on slicer selections (like "End Date"(From disconnted date table, based on user slelected End date, visual should show last 1, yr, 3yr, 5 yr etc) and "Period"), and I’m struggling to pass slicer values properly into the calculated columns ( I knew we can't pass slicer value into cal column but i want to capture slicer value and pass into my caluculation to get the desired result). I would greatly appreciate any guidance on how to achieve the expected output while considering slicer selections.

Example scenario:

• Fact/Main Table: Manager Name = Test1, data from Jan 2019 to Current Date.

• Ref Data Table: Contains effective dates for managers like Test1.

• Date table ( x axis - I considered date from date table and it is joined to Fact table based on date)
• Disconnect date table ( for End date slicer,  based on user slelected End date, visual should show last 1, yr, 3yr, 5 yr etc)
• Period Static table (static column 1yr, 3, yr, 5 yr, since inception)

• The rank should behave as described above, dynamically adjusting to slicer changes (such as "End Date" and "Period"). For example:

  • If the user selects an End Date of 31-Mar-2025 and a Period of 1 year, the visual should only filter data from Mar 2024 to Mar 2025.

  • Similarly, if the user selects a Period of 3 years, the visual should filter data from Mar 2022 to Mar 2025.

  • The rank calculation and reset logic should then work as per the requirements mentioned above, considering the selected date range.

Any guidance, ideas, or better approaches would be truly appreciated!

Thank you so much for your time and support.

[v-priyankata]

Hi @bchappidi86 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-priyankata]

Hi @bchappidi86 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-priyankata]

Hi @bchappidi86 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[DataNinja777]

Hi @bchappidi86 ,

Sure! Based on your requirement, you want a DAX measure that dynamically calculates a resettable rank and a final measure that pulls in tracking error from the reference table and carries forward the last block’s result. This needs to fully respond to the slicers for End Date (from a disconnected table) and the Period (1Y, 3Y, etc.), and reset the rank each time an effective date from the reference table matches the manager and date in the main table.

You start by capturing the selected End Date from the slicer:

SelectedEndDate = MAX('Disconnect Date Table'[Date])

Then, based on the selected Period value (assuming values like 1 = 1 year, 3 = 3 years), you calculate the start date for filtering:

StartDate = 
SWITCH(
    SELECTEDVALUE('Period'[Order]),
    1, EDATE([SelectedEndDate], -12),
    3, EDATE([SelectedEndDate], -36),
    5, EDATE([SelectedEndDate], -60),
    -- fallback for "Since inception"
    DATE(2000,1,1)
)

Now, filter the main Fact Table to apply the user-defined range:

FilteredTable =
FILTER(
    'Fact Table',
    'Fact Table'[Date] >= [StartDate] &&
    'Fact Table'[Date] <= [SelectedEndDate]
)

To find the last effective date from the Ref Data table up to the current row’s date for each manager, define the block ID like this:

BlockID = 
CALCULATE(
    MAXX(
        FILTER(
            'Ref Data',
            'Ref Data'[Manager Name] = SELECTEDVALUE('Fact Table'[Manager Name]) &&
            'Ref Data'[Effective Date] <= MAX('Fact Table'[Date])
        ),
        'Ref Data'[Effective Date]
    )
)

Next, you define the rank for each row within its block using RANKX:

BlockRank =
RANKX(
    FILTER(
        ALL('Fact Table'),
        [BlockID] = CALCULATE(MAX([BlockID])) &&
        'Fact Table'[Manager Name] = SELECTEDVALUE('Fact Table'[Manager Name])
    ),
    'Fact Table'[Date],
    ,
    ASC
) - 1

To carry forward the final measure value from the previous block (say, 1 std Final Measure), calculate the last value from the previous block:

PrevBlockFinalValue =
VAR CurrentBlock = [BlockID]
VAR PrevBlock =
    CALCULATE(
        MAXX(
            FILTER(
                VALUES('Ref Data'[Effective Date]),
                'Ref Data'[Effective Date] < CurrentBlock
            ),
            'Ref Data'[Effective Date]
        )
    )
RETURN
    CALCULATE(
        MAXX(
            FILTER(
                'Fact Table',
                [BlockID] = PrevBlock &&
                'Fact Table'[Manager Name] = SELECTEDVALUE('Fact Table'[Manager Name])
            ),
            [1 std Final Measure]
        )
    )

)
Now for the final calculation, multiply the tracking error from Ref Data for the current block by the sum of rank and previous block’s final value:

FinalMeasure =
VAR CurrentTrackingError =
    CALCULATE(
        MAX('Ref Data'[Tracking error]),
        FILTER(
            'Ref Data',
            'Ref Data'[Manager Name] = SELECTEDVALUE('Fact Table'[Manager Name]) &&
            'Ref Data'[Effective Date] = [BlockID]
        )
    )
RETURN
    CurrentTrackingError * ([BlockRank] + [PrevBlockFinalValue])

Everything here should be written as measures, not calculated columns, to ensure they respond dynamically to the slicers. Let me know your exact measure names and if you'd like me to format this into copy-paste-ready Power BI measures using your column names.

Best regards,

[bchappidi86]

Thanks @DataNinja777 for your reply. 

You’ve understood the requirement correctly, and I appreciate your attention to detail.

I was able to get the final output ("1 Std Final") using calculated columns, but it is currently working only when the Period slicer is set to "Since Inception." When selecting other periods like 1 year, 3 years, or 5 years, it is not behaving as expected.

I have uploaded a sample mock-up PBIX file with sample data and my calculations. You can find it here. here. https://drive.google.com/drive/folders/1qbvZrWe8ttkfrmFGZI0m_exITFin8LtT?usp=sharing

Specifically, the Rank Pre and Rank Final calculations are not dynamically adjusting based on slicer selections (such as "End Date" and "Period").

To clarify my requirement:

1. The rank should start at 0 for the minimum month of the selected period and End date (Slicers).

2. When there is a matching effective date in the reference data table, the rank should reset and start from 1, 2, 3, etc., instead of starting from 0.

3. Additionally, if there is any rank reset, I would like the previous last value to be populated in the current rows to calucualte "1 STD Final" value, as shown in the attached screenshot.

Would it be possible for you to update the mock-up PBIX file with your logic? Your assistance with this would be greatly appreciated.

Thank you again for your time and help.

[DataNinja777 · Accepted solution]

Hi @bchappidi86 ,

Thanks for sharing the file.  Start by creating a measure to capture the End Date selected from your disconnected date slicer. You can do this with the following:

Selected End Date = MAX('Disconnect Date Table'[Date])

Next, define the Start Date based on the selected period. Assuming the Period[Order] column contains numeric values like 1 for 1 Year, 3 for 3 Years, and 5 for 5 Years, you can calculate the start date using:

Selected Start Date =
VAR _end = [Selected End Date]
VAR _period = SELECTEDVALUE(Period[Order])
RETURN
    SWITCH(
        TRUE(),
        _period = 1, EDATE(_end, -12),
        _period = 3, EDATE(_end, -36),
        _period = 5, EDATE(_end, -60),
        _period = 0, DATE(2000, 1, 1),
        DATE(2000, 1, 1)
    )

Then, for each date in the visual, define the Effective Block Start Date. This value is the most recent effective date from 'Ref Data' that is less than or equal to the current date for the same manager:

Effective Block Start Date =
CALCULATE(
    MAX('Ref Data'[Effective Date]),
    FILTER(
        ALL('Ref Data'),
        'Ref Data'[Manager Name] = SELECTEDVALUE('Fact Table'[Manager Name]) &&
        'Ref Data'[Effective Date] <= MAX('DateTable'[Date])
    )
)

Once you've identified the block per row, you can assign the rank within each block using this measure. It ranks dates in the same segment (i.e., same Effective Block Start Date) that also fall within the selected period:

Rank Final =
VAR _currentBlock = [Effective Block Start Date]
RETURN
    RANKX(
        FILTER(
            ALL('DateTable'),
            [Effective Block Start Date] = _currentBlock &&
            'DateTable'[Date] >= [Selected Start Date] &&
            'DateTable'[Date] <= [Selected End Date]
        ),
        'DateTable'[Date],
        ,
        ASC
    )

To calculate the tracking error to apply for the current block, use this measure to fetch it from 'Ref Data':

Tracking Error Current Block =
CALCULATE(
    MAX('Ref Data'[Tracking error]),
    FILTER(
        ALL('Ref Data'),
        'Ref Data'[Manager Name] = SELECTEDVALUE('Fact Table'[Manager Name]) &&
        'Ref Data'[Effective Date] = [Effective Block Start Date]
    )
)

Then create a measure to fetch the final value from the previous block. This is used to continue the rolling total in the next segment:

Previous Block Final =
VAR _currBlock = [Effective Block Start Date]
VAR _prevBlock =
    CALCULATE(
        MAX('Ref Data'[Effective Date]),
        FILTER(
            ALL('Ref Data'),
            'Ref Data'[Effective Date] < _currBlock &&
            'Ref Data'[Manager Name] = SELECTEDVALUE('Fact Table'[Manager Name])
        )
    )
RETURN
    CALCULATE(
        MAX([1 STD - Final]),
        FILTER(
            ALL('DateTable'),
            [Effective Block Start Date] = _prevBlock &&
            'DateTable'[Date] >= [Selected Start Date] &&
            'DateTable'[Date] <= [Selected End Date]
        )
    )

Finally, define the main measure that uses all the previous components. This measure calculates the tracking error multiplied by the rank and includes the previous segment’s final value only for the first row of a new block:

1 STD - Final =
VAR _rank = [Rank Final]
VAR _track = [Tracking Error Current Block]
VAR _prev = [Previous Block Final]
RETURN
    IF(
        ISBLANK(_track),
        BLANK(),
        IF(
            _rank = 1 && NOT ISBLANK(_prev),
            _track * (_rank + _prev),
            _track * _rank
        )
    )

Once these measures are in place, use DateTable[Date] in your visual’s axis, and make sure all slicers and visuals are set to filter using the 'DateTable'. The result will be a fully dynamic ranking system that resets based on effective dates, carries forward previous values where needed, and responds seamlessly to slicers like End Date and Period.

Best regards,

[johnbasha33]

@bchappidi86 
Dynamic Date Range (based on Slicers)

Selected_End_Date =
MAX('Disconnected End Date Table'[Date])

Selected_Period =
MAX('Period Table'[Period]) -- Assume this is like "1 Year", "3 Year", etc.

Selected_Start_Date =
SWITCH(
TRUE(),
Selected_Period = "1 Year", EDATE([Selected_End_Date], -12),
Selected_Period = "3 Year", EDATE([Selected_End_Date], -36),
Selected_Period = "5 Year", EDATE([Selected_End_Date], -60),
Selected_Period = "Since Inception", MIN('Fact'[Date]) -- or hardcode if needed
)
Create a measure for Block Reset (Effective Date Match)
Is_Effective_Date =
IF(
CALCULATE(
COUNTROWS('Ref Data'),
'Ref Data'[Manager] = SELECTEDVALUE('Fact'[Manager]),
'Ref Data'[Effective Date] = SELECTEDVALUE('Fact'[Date])
) > 0,
1,
0
)

Build Block ID dynamically

You can accumulate a running total of "Is_Effective_Date" to create a BlockID:
BlockID =
CALCULATE(
SUMX(
FILTER(
ALL('Fact'),
'Fact'[Date] <= EARLIER('Fact'[Date]) &&
'Fact'[Manager] = EARLIER('Fact'[Manager])
),
[Is_Effective_Date]
)
)

Rank within Block

Now, generate the dynamic Rank:
Dynamic_Rank =
RANKX(
FILTER(
ALL('Fact'),
[BlockID] = EARLIER([BlockID]) &&
'Fact'[Manager] = EARLIER('Fact'[Manager])
),
'Fact'[Date],
,
ASC
) - 1

Carry Forward Previous Block Last Rank

This part is a bit more complex but in essence:

• For each block, find the max rank of the previous block (BlockID - 1).

• Carry it over to the next block.

You can create a measure:

Previous_Block_Last_Rank =
VAR PrevBlock = [BlockID] - 1
RETURN
CALCULATE(
MAX([Dynamic_Rank]),
FILTER(
ALL('Fact'),
[BlockID] = PrevBlock &&
'Fact'[Manager] = SELECTEDVALUE('Fact'[Manager])
)

Final Calculation

If your final output needs (Rank + Previous Block Last Value) * Some Value, it would look like:
Final_Calc =
([Dynamic_Rank] + [Previous_Block_Last_Rank]) * [Tracking Error]

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

)

[bchappidi86]

Thank you @johnbasha33 for your reply to my post.  Apologies, but I couldn’t fully understand the logic you shared.

Here is the link to the mock-up PBIX file with sample data that I created:

https://drive.google.com/drive/folders/1qbvZrWe8ttkfrmFGZI0m_exITFin8LtT?usp=sharing

If possible, could you kindly update the mock-up PBIX file with your logic?
Your assistance would be truly appreciated. Thank you once again for your time and support!

Kind regards,

Bharath Ch