cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
deepak21 Frequent Visitor
Frequent Visitor

To calculate the SUM of the most recent entries

Dear community,

 

I am currently preparing a weekly report in PBI desktop, in which I would add fresh rows to the excel source every week.

 

My problem is that I am not able to create a DAX formula which would filter the most recent entries (basically the values corresponding the current week) without editing the formula every week.

 

I am currently using:

 

//

current sum = CALCULATE(SUM('Table'[VALUES]),FILTER('Table','Table'[WKNUM]=CALCULATE(MAX('Table'[WKNUM]),ALL('Table'))))

 

//

 

But I am not sure what to do when fresh values for 01.2020 would arrive since the MAX between 01.2020 and 52.2019 would be 52.2019 and not 01.2020......

 

 

Is there a way to go about it?

 

ex. 

WKNUMVALUES
WK28.201934
WK28.201968
WK28.201955
WK28.20193
WK29.201959
WK29.201977
WK30.20193
WK30.20192
WK30.201955
WK31.201934
WK31.201913
WK31.20199
WK32.201980
WK32.201939
WK32.201995
WK32.201929
WK32.201994
WK32.201975
WK32.201919

 

 

 

Is there a way to go about it?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: To calculate the SUM of the most recent entries

Hi @deepak21  ,

 

You can create measure like DAX below firstly.

 

Sum value = CALCULATE(SUM(Table1[VALUES]),FILTER(ALLSELECTED(Table1),Table1[WKNUM]=MAX(Table1[WKNUM])))

Then set the Top 1 filter for [WKNUM] in Visual level filter.

 

 

64.png

 

Best Regards,

Amy

 

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

View solution in original post

2 REPLIES 2
amitchandak Super Contributor
Super Contributor

Re: To calculate the SUM of the most recent entries

If you create a column like YYYY & WEEKNUM (Use padding to make sure 2 digits), the max will always make sure.

 

Also if you use filter pane. In that, if you drag a date you have Advance and the relative option to set recent date, week and month.   You can set that at visual, page or report level

 

Screenshot 2019-10-11 23.24.39.png

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

Community Support Team
Community Support Team

Re: To calculate the SUM of the most recent entries

Hi @deepak21  ,

 

You can create measure like DAX below firstly.

 

Sum value = CALCULATE(SUM(Table1[VALUES]),FILTER(ALLSELECTED(Table1),Table1[WKNUM]=MAX(Table1[WKNUM])))

Then set the Top 1 filter for [WKNUM] in Visual level filter.

 

 

64.png

 

Best Regards,

Amy

 

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

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 160 members 1,597 guests
Please welcome our newest community members: