latest reported value for a given week

[RLangkemper]

Hi all,

I have a table, with a given report date and a measured quantity for that date. The table also comes with a weeknumber and year column. Now I want to chart the data exactly like that, each measured value by week by year. So far so good, the issue arises with some weeks having multiple measurements (so two or more report dates in a week, causing for the chart to show a spike with double the quantity. I want the chart to just show the last measurement of each week in such case.

I was trying to write a measure to pick up the quantity for a max date within a week, but getting stuck. Anyone have any suggestion?

Thanks,

Richard

[V-lianl-msft · Accepted solution]

Hi @RLangkemper ,

Based on your description, you want to pick up the quantity for a max date within a week. You can try to use the DAX below:

Measure 2 = 
var max_date = CALCULATE(
    LASTNONBLANK(Sheet3[date],1),
    FILTER(ALL(Sheet3),
        COUNTROWS(FILTER(Sheet3,EARLIER(Sheet3[week_num])=Sheet3[week_num]))))
return IF(MAX(Sheet3[date])=max_date,MAX(Sheet3[report_count]))

You can also refer to the .pbix

Best Regards,

Liang

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