Need help on displaying current week value in card but other week values are not 0

[tools12345]

Hello everyone, I really need your expertise on this one. I have been trying around and am just new to PBI and I have the below measure created: 

It displayed the week value I needed on the card, but the other week values returned to 0. How can I display all the other weeks with the correct values? Any thoughts or suggestions? 

ap_latest_usd =

VAR max_date = MAXX('table','table'[WY2])

VAR latest_ap = CALCULATE(SUM('table'[usd_convert])+0,'table'[WY2]=WEEKNUM(TODAY())-1)

Return latest_ap

[v-mengzhu-msft · Accepted solution]

Hi @tools12345 ,

VAR latest_ap = CALCULATE(SUM('table'[usd_convert])+0,filter('table', [WY2]=WEEKNUM(TODAY())-1))

please try this to check if it works. 

[tools12345]

Hi @v-mengzhu-msft thank you for the help and suggestions! Appreciate it. Unfortunately, I am still not getting the desired result I want to display. Please see image attached. 

[v-mengzhu-msft]

Hi @tools12345 ，

I understand that you want all cards to show the correct value, not the "0" value.

I'd love to be able to help you on this issue,you can try this measure:

VAR latest_ap = CALCULATE(SUM('table'[usd_convert])+0,filter(all('table'), [WY2]=WEEKNUM(TODAY())-1))

If there still exists something wrong, I'll be happy that you provide me with a sample data, because it would be very helpful for me to study your problem.

You can provide sample data by uploading a PBIX file or an Excel file.

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[tools12345]

Hi @v-mengzhu-msft thank you for your insights, I appreciate it! I tried to apply the measure but what it did was copy the same value to other weeks, highlighted in the red box. I'd like to achieve the same results highlighted in the green box. Please see the below image. 

[amitchandak]

@tools12345 , for this week this can work

This Week =
CALCULATE(SUM('netsuite_powerbi apv_poitem_pomiles_pr'[usd_convert])+0, WEEKNUM('netsuite_powerbi apv_poitem_pomiles_pr'[TransDate])=WEEKNUM(TODAY()))

and for last week , if date is not filtered

This Week =
CALCULATE(SUM('netsuite_powerbi apv_poitem_pomiles_pr'[usd_convert])+0, WEEKNUM('netsuite_powerbi apv_poitem_pomiles_pr'[TransDate])=WEEKNUM(TODAY()) -1 )

Other wise better to have date table joined to date of your table with follwoing columns

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

and measures
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[tools12345]

Hi Sir, appreciate your thoughts and reply! Unfortunately, tried to apply the first 2 measures recommended above but got the same results, other weeks returned 0 in value. Any possible workaround we can do on the measure instead of creating another new column? Thank you so much! 

[amitchandak]

@tools12345 , Please create them as measures, not columns. And This week based on today will only have value for the current week. You can consider This week Vs last week

[tools12345]

Hi Sir, yes I created them as measures, using this one provided, but still got the other week values as 0 as image below. Thank you for the patience! 

This Week =
CALCULATE(SUM('netsuite_powerbi apv_poitem_pomiles_pr'[usd_convert])+0, WEEKNUM('netsuite_powerbi apv_poitem_pomiles_pr'[TransDate])=WEEKNUM(TODAY()))