Dax help

[sarath_chandra]

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

[BeaBF · Accepted solution]

@sarath_chandra See the new pbix.

BBF

[FreemanZ]

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

[Jihwan_Kim · Accepted solution]

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] ) )
)

[BeaBF]

@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

[sarath_chandra]

Thanks@BeaBF ,you added PN, usage measure ,difference,date in columns ... but i dont need date ( i need usage for selected week ,usage for past week of selected week date as columns) can you please alter the dax ? please

[BeaBF · Accepted solution]

@sarath_chandra See the new pbix.

BBF

[sarath_chandra]

great thanks for your support

[sarath_chandra]

@BeaBF plz help me out 

[sarath_chandra]

@tamerj1 @Jihwan_Kim @FreemanZ plz help me in this