Forum Discussion

SuperCal99's avatar
SuperCal99
Helper I
4 years ago

Week-to-date Measure for Card Visual

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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      SuperCal99
      Helper I
      4 years ago

      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)