Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

multiple year to dates lines in one visual

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 m...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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/