multiple year to dates lines in one visual

[Anonymous]

Community:   Any idea how to adjust this formula so that it does not show (the previous years) beyond the current date?    As you can see, the orange value is this year and stops after there are no more values.   This is perfect.   I would like the other years to also stop at the same point in time, based on week, or month.   How would I constrain multiple dates like that based on the formula below?

Cumulative All Years = CALCULATE(SUM(Shipments[ShippedPrice]),FILTER(ALL(Shipments),Shipments[DateShipped]<=MAX(Shipments[DateShipped])),VALUES(Shipments[Date Shipped Year]))

[plainly]

Hello,

If you want the data to stop to a  certain month / week that is exactly what you should write to the filter.

E.g. for the month level ( you are likely to guess how to make it with && condition for  a week too):

FILTER('Calendar';'Calendar'[Month Number] <= MONTH(today()))

'Calendar'[Month Number] you can replace with MONTH(table[date])

See more info on how to calculate with dates: 

https://www.plainlyresults.com/blog/power-bi-dax-how-to-calculate-and-filter-based-on-dates/

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

@Ashish_Mathur   Unfortunately I cannot due to sensitive data

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Try the following formula:

Cumulative All Years = 
var t = 
  CALCULATE(
    SUM(Shipments[ShippedPrice]),
    FILTER(
      ALL(Shipments),
      Shipments[DateShipped] <= MAX(Shipments[DateShipped])
    ),
    VALUES(Shipments[Date Shipped Year])
  )
var result = 
  IF(
    MONTH(MAX(Shipments[DateShipped]))<=MONTH(LASTDATE(ALL(Shipments[DateShipped]))),
    t
  )
return result

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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