Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sarath_chandra
Helper III
Helper III

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

sarath_chandra_0-1673443905521.png

Excepted output

sarath_chandra_1-1673443997078.png

plz help me out . plz

2 ACCEPTED SOLUTIONS

Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_1-1673446862872.png

 

 

Jihwan_Kim_0-1673446829038.png

 

 

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

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


View solution in original post

8 REPLIES 8
FreemanZ
Super User
Super User

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
Super User
Super User

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.

 

Jihwan_Kim_1-1673446862872.png

 

 

Jihwan_Kim_0-1673446829038.png

 

 

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

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


BeaBF
Solution Sage
Solution Sage

@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

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

 

@sarath_chandra See the new pbix.

 

BBF

great thanks for your support

sarath_chandra
Helper III
Helper III

@BeaBF plz help me out 

 

sarath_chandra
Helper III
Helper III

@tamerj1 @Jihwan_Kim @FreemanZ plz help me in this

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.