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 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]))

 
 
 
 
 
 
 
 
 
 
 
 
 

 
 

 

 
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.

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Ashish_Mathur   Unfortunately I cannot due to sensitive data

      • v-kkf-msft's avatar
        v-kkf-msft
        Icon for Community Support rankCommunity Support
        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/