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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
tools12345
Helper I
Helper I

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

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
powerbi-apvs.png
 
 
1 ACCEPTED SOLUTION
v-mengzhu-msft
Community Support
Community Support

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. 

View solution in original post

8 REPLIES 8
v-mengzhu-msft
Community Support
Community Support

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. 

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. powerbi result.png

v-mengzhu-msft
Community Support
Community Support

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

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. powerbi card q.png

amitchandak
Super User
Super User

@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

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! 

@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

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()))
output1.png

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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

Top Solution Authors