Forum Discussion

julianhoewel_95's avatar
julianhoewel_95
Frequent Visitor
4 years ago
Solved

Cumulative Sum for specific date range

Hello,

in my BI Dashboard I am showing the cumulative revenue per financial year, which is working fine. 
My problem is that I also want to show the cumulative expense, which is 660000 per month, but I don't know how to embed that into that visual. My idea is to write a DAX for each financial year, so I could also add a DAX for the cumulative expense. Right now I have the financial year in the visual's legend, so I cannot add a secondary value...
Is there anyway to also add the expense in the visual I already have or is the only option to write separate DAX for each financial year? If so, how would the DAX for the cumulative sum of a specific date range look?

Thanks for any help!
Julian

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, julianhoewel_95 

    In your formula, you may need to create a variable ‘a’ to dynamically read the current maximum date.

    TotalRevGJ1920 = 
VAR a =
    CALCULATE (
        MAX ( ExportRechnungen[Rechnungdatum] ),
        FILTER ( ExportRechnungen, ExportRechnungen[GJ] = "GJ 2019/2020" )
    )
RETURN
    CALCULATE (
        SUM ( ExportRechnungen[Netto] ),
        FILTER (
            ALLSELECTED ( ExportRechnungen ),
            ExportRechnungen[Rechnungdatum] <= a
                && ExportRechnungen[GJ] = "GJ 2019/2020"
        )
    )

    Please check my sample file for more details.

     

    Best Regards,
    Community Support Team _ Eason

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    julianhoewel_95 , I doubt you can add just a measure when a legend is used, even a secondary axis is not allowed when you more than one measure in case of line

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    4 years ago

    Hi, julianhoewel_95 

    Yes. In your case, if the cumulative expense is a constant value, you can consider adding auxiliary lines through the analytics pane. If not, the best way is to cancel the use of the 'Legend' field, and then replace the original field in 'Values‘ with multiple measures(GJ2019/2020,GJ2020/2021,GJ2021/2022 + cumulative expense).

     

    Best Regards,
    Community Support Team _ Eason

     

    • julianhoewel_95's avatar
      julianhoewel_95
      Frequent Visitor
      4 years ago

      Thanks for your reply. The cumulative expense isn't constant so I guess the second option is the solution.
      While implementing that DAX I faced another issue, somehow the cumulative sum is a constant value for the recent financial years (for example "GJ 2019/2020"), the current financial year is working fine.
      I have categorized the invoices (ExportRechnungen) by financial year, so it is only filtering data for the desired financial year but still this is always a constant value, whatever date range I take ... 
      Any solution forthat DAX?

      Thanks!

      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        4 years ago

        Hi, julianhoewel_95 

        In your formula, you may need to create a variable ‘a’ to dynamically read the current maximum date.

        TotalRevGJ1920 = 
VAR a =
    CALCULATE (
        MAX ( ExportRechnungen[Rechnungdatum] ),
        FILTER ( ExportRechnungen, ExportRechnungen[GJ] = "GJ 2019/2020" )
    )
RETURN
    CALCULATE (
        SUM ( ExportRechnungen[Netto] ),
        FILTER (
            ALLSELECTED ( ExportRechnungen ),
            ExportRechnungen[Rechnungdatum] <= a
                && ExportRechnungen[GJ] = "GJ 2019/2020"
        )
    )

        Please check my sample file for more details.

         

        Best Regards,
        Community Support Team _ Eason