Forum Discussion

jordanxie's avatar
jordanxie
Icon for Helper I rankHelper I
6 years ago
Solved

Calculate Sum by Last Date

Hi,

 

I just can't seem to get this to work. 

 

I have a trend chart and bar chart that has total by 'Plant' let's say based on the last date.

 

Don't want to filter the bar chart by 'last date' because would like the trend chart to show the trend over time if a single 'Plant' is 'clicked' on that bar chart. So I created a measure to sum up the value for last date. 

 

DAX = Calculate (SUM([Value]), LASTDATE([Report Date])

 

Is the formula wrong?

Another situation is that when I filter by a Person ID, the bar chart does not add up and it actually shows value from a historic date.

 
Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi jordanxie ,

     

    We create a sample and have some tests, you can refer the measure.

     

     

    Measure = 
var max_date = CALCULATE(MAX('Table'[Report Date]),ALLSELECTED('Table'))
return
CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Report Date]=max_date))

     

     

    The result like this,

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

     

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

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

2 Replies

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi jordanxie ,

     

    We create a sample and have some tests, you can refer the measure.

     

     

    Measure = 
var max_date = CALCULATE(MAX('Table'[Report Date]),ALLSELECTED('Table'))
return
CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Report Date]=max_date))

     

     

    The result like this,

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

     

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

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

  • jordanxie's avatar
    jordanxie
    Icon for Helper I rankHelper I
    6 years ago

    Would like to add that when I filter the data in PBI query, it shows the correctly. It is just the bar chart that adds an additional bar from previous date (not the last date)