Custom Comparison Period calculations not working correctly in visuals with dates

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!