Custom Comparison Period calculations not working correctly in visuals with dates

[Nawalasim8]

Hello, i have a dashbaord that is using a comparison date table, the configuration is such that Im able to select any custom dates to compare my current date range with. Im then calculating % increase/decrease based on the 2 date ranges selected. This seems to be working fine for visuals where the measure isnt broken down by a date dimension, however when i have visuals such as the following:

For the comparison period it shows me the same value across different months. I understand this is because im using the months from the primary date dimension table but ideally, I require for this to compare as follows for the above example:

compares oct 2024 to january 2024, nov 2024 to feb 2024, and dec 2024 to nothing since in the comparison period only 2 months are selected. However, currently its comparing each month against the total of jany-feb 2024 which is not what i want.

Ive tried different work arounds but cant seem to arrive at a solution here. Any help would be appreciated please.

Currently im using a second date table and am using an inactive relationship and the following DAX for the comparison period calculations: (sample)
pageviews_previous = CALCULATE(SUM(total_pageviews_by_date[screen_page_views]),ALL(dimdate),USERELATIONSHIP(total_pageviews_by_date[date], dimdate2[date]))

[robin_william]

You’re on the right track by using a second date table and inactive relationship for custom comparisons, but to achieve the month-to-month shift logic you're after, consider adjusting your DAX to dynamically align month offsets using alternatives or shifting with . You might also find a Real Estate Survey helpful when modeling comparative time data structures in broader applications. Let me know if you'd like a more refined measure suggestion!

[tharunkumarRTK]

@Nawalasim8 

Your requirement has few unknowns for example, what if there are 10 months selected in comparison date filter and 1 month in normal date filter? I am assuming then first month out 10 months should compared with the only month that is selected in the normal date filter 

this one of the approach that you an follow 

Step 1: Configure Relationships

1. Only relate dimdate to the fact table using an active relationship on the date column.
2. Keep dimdate2 disconnected from the fact table to allow custom logic for comparison period calculations.

Step 2: Capture Selected Min & Max Dates

Extract the minimum and maximum selected dates from both dimdate and dimdate2 slicers.

VAR __MinActualDate = MIN(dimdate[Date])
VAR __MaxActualDate = MAX(dimdate[Date])
VAR __MinComparisonDate = MIN(dimdate2[Date])
VAR __MaxComparisonDate = MAX(dimdate2[Date])

Step 3: Create a Table Variable for the Actual Period

This table will store page views per month in the actual period and assign a rank based on YearMonth (yyyymm)

VAR __ActualMonthsInSelection = 
    ADDCOLUMNS(
        FILTER( 
            ALL(dimdate),  
            dimdate[Date] >= __MinActualDate && dimdate[Date] <= __MaxActualDate 
        ), 
        "Page Views", 
        CALCULATE(SUM(total_pageviews_by_date[screen_page_views])),
        "Rank Year Month", 
        CALCULATE(RANKX( 
            ALL(dimdate[YearMonth]), 
            dimdate[YearMonth], , DESC, DENSE 
        ))
    )

Step 4: Create a Table Variable for the Comparison Period

This table will store page views per month in the comparison period and assign a rank based on YearMonth

VAR __ComparisonMonthsInSelection = 
    ADDCOLUMNS(
        FILTER( 
            ALL(dimdate2),  
            dimdate2[Date] >= __MinComparisonDate && dimdate2[Date] <= __MaxComparisonDate 
        ), 
        "Page Views", 
        CALCULATE(SUM(total_pageviews_by_date[screen_page_views]), 
                  USERELATIONSHIP(total_pageviews_by_date[Date], dimdate2[Date])
        ),
        "Rank Year Month", 
        CALCULATE(RANKX( 
            ALL(dimdate2[YearMonth]), 
            dimdate2[YearMonth], , DESC, DENSE 
        ))
    )

Step 5: For the end result, 

Var __SelectedYearMonth = max(dimdate[YearMonth]
Var __SelectedYearMonthRank = SUMX(FILTER(__ActualMonthsInSelection, dimdate[YearMonth] = __SelectedYearMonth ), [Rank Year Month] )
Var __Result = SUMX(Filter(__ComparisonMonthsInSelection, [Rank Year Month] = __SelectedYearMonthRank ), [Page Views])
Return __Result

Please make changes to the above code as per your model, for further assistance, please share the pbix file with sample data

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!