Week-to-date Measure for Card Visual

[SuperCal99]

Hi there,

Looking for some DAX help.

I have the following measure to calculate Week-to date figures, which works perfectly fine for a table visual

WTD =

IF (

HASONEVALUE ( 'Date Dim'[Year] ) && HASONEVALUE ( 'Date Dim'[Month]),

CALCULATE (

SUM (Incident[Resolved COUNT] ),

FILTER (

ALL ( 'Date Dim'),

'Date Dim'[Year]= VALUES ( 'Date Dim'[Year] )

&& 'Date Dim'[Month] = VALUES ( 'Date Dim'[Month])

&& 'Date Dim'[Date] <= MAX ( 'Date Dim'[Date] )

)

),

BLANK ()

)

However, I would also like to show the Week-to-date value in a card/Multi-row card visual, which resets at the start of each week.

My DAX knowledge isnt great so wondering is there a measure i can use?

Thanks in advance for any advice or guidance

[amitchandak]

@SuperCal99 , with help from these columns in date table

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

Weekday = WEEKDAY([Date],2)
measures

WTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[WeekDay]<=max('Date'[WeekDay])-1))
LWTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[WeekDay]<=max('Date'[WeekDay])-1 ))

[SuperCal99]

Thanks @amitchandak for the reply and sorry for the late response.

I ended up going with the below query for wtd, however when published to service and refreshes daily, it returns zero but when i refresh in powerbi desktop, it appears correctly. Any reason why this is?

WeekTD - Resolved =

COALESCE(IF (

HASONEVALUE ( CALENDER[Year] ) && HASONEVALUE ( CALENDER[Week Number] ),

CALCULATE (

SUM ( Incident[Resolved COUNT] ),

FILTER (

ALL ( CALENDER ),

'CALENDER'[Year] = VALUES ( CALENDER[Year] )

&& CALENDER[Week Number] = VALUES ( CALENDER[Week Number] )

&& CALENDER[Created DATE] <= MAX (CALENDER[Created DATE] )

)

),

BLANK ()

),0)