topic Re: Need help on displaying current week value in card but other week values are not 0 in DAX Commands and Tips

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

tools12345 — Mon, 27 Jun 2022 05:02:58 GMT

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

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

amitchandak — Mon, 27 Jun 2022 05:04:54 GMT

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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

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

tools12345 — Mon, 27 Jun 2022 05:16:23 GMT

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!

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

amitchandak — Mon, 27 Jun 2022 05:23:32 GMT

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

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

tools12345 — Mon, 27 Jun 2022 05:45:40 GMT

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

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

Anonymous — Thu, 30 Jun 2022 07:17:46 GMT

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

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

tools12345 — Mon, 04 Jul 2022 01:03:06 GMT

Hi Anonymous 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.

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

Anonymous — Mon, 04 Jul 2022 09:30:51 GMT

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.

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

tools12345 — Fri, 08 Jul 2022 10:41:16 GMT

Hi Anonymous 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.