calculate sales for the same number of days prior to the selected date range

[Rana_Rumeel]

Hi Everyone,

I have a fact table which contains Sales and Dates, But there is no Date and Sales for Weekends.
I created a Custom Date Table using following DAX

Calendar_Dates = 
VAR DateRange = CALENDARAUTO()

RETURN 
ADDCOLUMNS(
    DateRange,
    "Year",YEAR([Date]),
    "Month",FORMAT([Date],"mmmm"),
    "Day",FORMAT([Date],"yyyy-mm-dd"),
    "Year Month", FORMAT([Date],"yyyy-mmmm"),
    "YearMonthSort",YEAR([Date])*100 + MONTH([Date]),
    "ShortName",FORMAT([Date],"ddd"),
    "IsWeekDay", NOT WEEKDAY( [Date] ) IN {1,7}
)

 there is one-Many relation ship between my Fact and Calendar table.

Problem Statement:
I need to calculate sales for the same number of days prior to the selected date range.
For Example if user selected 27 May 2024 then it must calculate sales for 24 May 2024.
If user selected 20 May 2024 till 24 May 2024 then it must calculate Sales for 13 May 2024 till 17 May 2024.

to Achive this I already created some measures

SelectedRangeStart = MIN(Calendar_Dates[Date])

SelectedRangeEnd = MAX(Calendar_Dates[Date])

NumberOfDaysSelected = 
CALCULATE(
    COUNTROWS('Calendar_Dates'),
    DATESBETWEEN('Calendar_Dates'[Date], [SelectedRangeStart], [SelectedRangeEnd]),
    NOT(WEEKDAY('Calendar_Dates'[Date], 2) IN {6, 7})  -- Exclude weekends
)

SalesPreviousPeriod = 
VAR StartDate = [SelectedRangeStart]
VAR DaysCount = [NumberOfDaysSelected]
VAR PreviousDates = 
    CALCULATETABLE(
        TOPN(DaysCount, 
            FILTER(
                'Calendar_Dates', 
                'Calendar_Dates'[Date] < StartDate &&
                NOT(WEEKDAY('Calendar_Dates'[Date], 2) IN {6, 7})
            ), 
            'Calendar_Dates'[Date], DESC
        )
    )
RETURN
    CALCULATE(
        [_Net],
        PreviousDates
    )

_Net = SUM('Top Customer Information'[Net])

But this measure is returning empty result.
Kindly suggest

Thanks,

[nandic]

@Rana_Rumeel could you try this calculation:

SalesPreviousPeriod =

VAR StartDate = [SelectedRangeStart]

VAR DaysCount = [NumberOfDaysSelected]

VAR PreviousDate = StartDate - DaysCount

RETURN

    CALCULATE(

        [_Net],

        DATESBETWEEN(Calendar_Dates[Date],PreviousDate,StartDate)

    )

Cheers,
Nemanja Andic

[Rana_Rumeel]

Hi @nandic, Thank you for your response.
I implemented your suggested solution but it is giving incorrect result in there are weekends before.

You can download .pbix file from Here... 

[Rana_Rumeel]

Any suggestion please...