Forum Discussion

RLangkemper's avatar
RLangkemper
Regular Visitor
6 years ago
Solved

latest reported value for a given week

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

 

 

Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    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

1 Reply

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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