Why Is there no Week To Date? Calculate same period last week Fiscal Petiod

[Mahmed1]

Hi Guys

Im not sure why we dont have a WTD function compared with YTD, MTD etc...

I want to be able to select the start of Week from my Fiscal calender (Starts in April)

and then want the sales for the

Week Selected and also the sales for same period last week

so say im only 4 days into the current week (Thursday) and i select Week commencing the 09/01

i need a measure that returns last week up until last Thursday too so same period last week as well as current week to date

how can I achieve this the best way bearing in mind the fiscal year (I do hasve an IsoWeek number column)

thank you

[Mahmed1]

@ppm1 Thank you

[ppm1]

These expressions show one way to do it, but you'll have to adapt to get your fiscal week.

WTD =
VAR thisdate =
    MAX ( 'Date'[Date] )
VAR SOW =
    thisdate - WEEKDAY ( thisdate ) + 1
RETURN
    CALCULATE (
        [Total Sales],
        ALL ( 'Date' ),
        'Date'[Date] >= SOW
            && 'Date'[Date] <= thisdate
    )

PWTD =
VAR thisdate =
    MAX ( 'Date'[Date] ) - 7
VAR SOW =
    thisdate - WEEKDAY ( thisdate ) + 1
RETURN
    CALCULATE (
        [Total Sales],
        ALL ( 'Date' ),
        'Date'[Date] >= SOW
            && 'Date'[Date] <= thisdate
    )

Pat

[Mahmed1]

Thank you

really appreciate it

this could work as i have a start of week column in my date table

My date table has future dates in there too so am i right in saying it would be better to get 'Thisdate' by using the MaxDate of SalesTable rather than DateTable?

[Greg_Deckler]

@Mahmed1 You'll have to create a week # column since it is a fiscal year and not a standard calendar year. Unless your weeks line up, then you can use WEEKNUM and grab all the dates with the same WEEKNUM that are less than TODAY. Hard to say without example data but generally you build helper columns into your date table. I have an example here that includes week #:

DAX Custom 445 Calendar - Microsoft Power BI Community

[Mahmed1]

Hi,

i tried this but when comparing to previous week for same period last week it does not work

Sales_PW =

VAR CurrentWeekNum = SELECTEDVALUE(calender([Fiscal_Week_Num]))

VAR CurrentYear = SELECTEDVALUE(calender[Fiscal_Year])

VAR MaxWeekNum = CALCULATE(

MAX(calender[Fiscal_Week_Num]),

ALL(calender))

RETURN

SUMX(

            FILTER(ALL(calender),

                       IF(CurrentWeekNum = 1,

                         calender[Fiscal_Week_Num] = MaxWeekNum && calender[Fiscal_Year]= CurrentYear - 1),

calender[Fiscal_Week_Num] = CurrentWeekNum - 1 && calender[Fiscal_Year]= CurrentYear)),

[Total_Sales])

[FreemanZ]

hi  @Mahmed1

you code seems having some issue,  try to change it like this:

Sales_PW =

VAR CurrentWeekNum = SELECTEDVALUE(calender([Fiscal_Week_Num]))

VAR CurrentYear = SELECTEDVALUE(calender[Fiscal_Year])

VAR MaxWeekNum = 

CALCULATE( AX(calender[Fiscal_Week_Num]), ALL(calender))

RETURN

SUMX(

    FILTER(

        ALL(calender),

        IF(

            CurrentWeekNum = 1,

            calender[Fiscal_Week_Num] = MaxWeekNum 

                && calender[Fiscal_Year]= CurrentYear - 1,

        calender[Fiscal_Week_Num] = CurrentWeekNum - 1 

            && calender[Fiscal_Year]= CurrentYear)

        )

    ),

    [Total_Sales]

)

 

p.s. please consider @someone, if you seek further suggestions. 

[Mahmed1]

Thank you for getting back to me..I do have a WeekNum column that starts from 01/04 and also a StartOfWeek date column

I need to now get WTD sales if i select this week and also week to date last week/same period last week