To calculate the SUM of the most recent entries

[Anonymous]

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. 

WKNUM	VALUES
WK28.2019	34
WK28.2019	68
WK28.2019	55
WK28.2019	3
WK29.2019	59
WK29.2019	77
WK30.2019	3
WK30.2019	2
WK30.2019	55
WK31.2019	34
WK31.2019	13
WK31.2019	9
WK32.2019	80
WK32.2019	39
WK32.2019	95
WK32.2019	29
WK32.2019	94
WK32.2019	75
WK32.2019	19

Is there a way to go about it?

[v-xicai · Accepted solution]

Hi @Anonymous  ,

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.

Best Regards,

Amy

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

[amitchandak]

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

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.