dax week-to-date last week as a variable

[Anonymous]

hi,

i have a dax measure for week-to-date (WTD) as follows:

   WTD =

   VAR CurrentDate = LASTDATE( 'Calendar'[Date] )

   VAR WeekDayNo = WEEKDAY( LASTDATE( 'Calendar'[Date]), 3)

   RETURN

      CALCULATE(

      SUM( Sales[Amount] ),

      DATESBETWEEN(

         'Calendar'[Date],

            DATEADD(

         CurrentDate,

         -1*WeekDayNo,

         DAY),

         CurrentDate)

         )

i also managed to create a dax measure for week-to-date for the previous week (WTD.LW) as follows:

   WTD.LW = CALCULATE (

      WTD,

         DATEADD (

         'Calendar'[Date],

         -7,

         DAY

         )

      )

however, if i wish to create week-to-date for the previous week (i.e. WTD.LW) as a variable, please advise on how to do so. 

tks, -nik

p.s.

my attempt to use the following dax still results in  week-to-date (WTD):

   VAR CurrentDate = LASTDATE( 'Calendar'[Date] )
   VAR WeekDayNo = WEEKDAY( LASTDATE( 'Calendar'[Date]), 3)
   VAR CWTD = CALCULATE(
     SUM( Sales[Amount],
         DATESBETWEEN(
         'Calendar'[Date],
            DATEADD(
            CurrentDate,
            -1*WeekDayNo,
            DAY),
            CurrentDate)
            )
      RETURN
         CALCULATE(
            CWTD,
            DATEADD (
            'Calendar'[Date],
            -7,
            DAY))

[amitchandak · Accepted solution]

@Anonymous , please refer if my blogs on the same can help

Power BI — WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may create measures like DAX below.

WTD =
CALCULATE (
    SUM ( Sales[Amount] ),
    FILTER (
        ALL ( 'Calendar' ),
        WEEKNUM ( 'Calendar'[Date] ) = WEEKNUM ( MAX ( 'Calendar'[Date] ) )
            && WEEKDAY ( 'Calendar'[Date] ) <= WEEKDAY ( MAX ( 'Calendar'[Date] ) )
    )
)



LWTD =
CALCULATE (
    SUM ( Sales[Amount] ),
    FILTER (
        ALL ( 'Calendar' ),
        WEEKNUM ( 'Calendar'[Date] )
            = WEEKNUM ( MAX ( 'Calendar'[Date] ) ) - 1
            && WEEKDAY ( 'Calendar'[Date] ) <= WEEKDAY ( MAX ( 'Calendar'[Date] ) )
    )
)

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

many2 tks, @v-xicai  & @amitchandak .

krgds, -nik

[amitchandak · Accepted solution]

@Anonymous , please refer if my blogs on the same can help

Power BI — WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123