Calculate Sales for same numbers of days prior to selected date in Slicer

[Rana_Rumeel]

Hi Everyone,

You can download .pbix file from Here...  

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,

[amitchandak]

@Rana_Rumeel , for that create workday rank first

Work Day = if(WEEKDAY([Date],2)>=6,0,1)
Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])
Work Date cont Rank = RANKX(ALL('Date'),[Work Date Cont],,ASC,Dense)

then try a measure like

//previous period workday
Same Date range last period =
var _max = MAXX(allselected('Date'), Date[Date])
var _maxRank = MAXX(allselected('Date'), Date[Work Date cont Rank])
var _diff = networkdays(MIN('Date'[date]),max('Date'[date]),1)
var _p_st_date = MINX(Filter(all('Date') ,Date[Work Date cont Rank] =_maxRank - _diff ), [Date])-1
var _p_end_date = _max-1
Return
CALCULATE(sum(Sales[Sales Amount]),all('Date'[date]),'Date'[date]>=_p_st_date && 'Date'[date]<= _p_end_date
)

Similar example

Power BI Workday vs Last Workday- https://youtu.be/MkYLT_GYIbM

Cont Work day Rank will help

Same Date range last period =
var _diff = datediff(MIN('Date'[date]),max('Date'[date]),DAY)
var _p_st_date = MINX('Date',DATEADD('Date'[date],-1*_diff,Day))-1
var _p_end_date = MAXX('Date',DATEADD('Date'[date],-1*_diff,Day))-1
Return
CALCULATE(sum(Sales[Sales Amount]),all('Date'[date]),'Date'[date]>=_p_st_date && 'Date'[date]<= _p_end_date
)

[Rana_Rumeel]

@amitchandak Thank you for your great response.
I tried to implement the solution which you suggested. But when I selected one week date range then it is calculating only sales for last 3 days while expected 5 days

Attached PBIX file has the suggested implementation.

Regards

[Rana_Rumeel]

Any suggestion or solution please.