Forum Discussion

sarath_chandra's avatar
sarath_chandra
Helper III
3 years ago
Solved

Dax help

Hi all, i have week date filter , if i select one date (current week date) and previous week date is prior week date that should be populated in table visual and i need difference of both dynamically .i need like that for every dynamic selection of week date

Excepted output

plz help me out . plz

DAX
Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

     

     

     

     

     

    Usage prior week: =
CALCULATE (
    [Usage current week:],
    OFFSET ( -1, ALL ( 'Calendar'[Year-Wk sort], 'Calendar'[Year-Wk] ) )
)

     

8 Replies

  • BeaBF's avatar
    BeaBF
    Super User
    3 years ago

    sarath_chandra Hi! Here is the measure to calculate dinamically the Difference:

     

    Difference =
    VAR SELECTEDWEEK = SELECTEDVALUE(DISTINCTTABLE[Date])
    VAR PREVWEEK = CALCULATE(MAX(DISTINCTTABLE[Date]), ALL(DISTINCTTABLE[Date]), DISTINCTTABLE[Date]< SELECTEDWEEK)
    VAR USAGESELECTED = CALCULATE([usage_measure],DISTINCTTABLE[Date] = SELECTEDWEEK)
    VAR USAGEPREV = CALCULATE([usage_measure],DISTINCTTABLE[Date] = PREVWEEK)
    RETURN USAGESELECTED - USAGEPREV
     
    There's only a problem with your view, it is not possibile with matrix in Power Bi, try another way to put it in a matrix.
    I attack the pbix file. If it is correct, please accept my reply as solution!
     
    BBF
  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

     

     

     

     

     

    Usage prior week: =
CALCULATE (
    [Usage current week:],
    OFFSET ( -1, ALL ( 'Calendar'[Year-Wk sort], 'Calendar'[Year-Wk] ) )
)

     

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi sarath_chandra 

    try like:

    UsageCurrentWeek =
VAR _date = MAX(TableName[week date])
SUMX(
    FILTER(TableName, TableName[week date] =  _date ),
    TableName[usage value]
)

UsagePriorWeek =
VAR _date = MAX(TableName[week date])
SUMX(
    FILTER(TableName, WEEKNUM(TableName[week date]) =  WEEKNUM(_date) -1 ),
    TableName[usage value]
)

difference = UsageCurrentWeek - UsagePriorWeek