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 mo...
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

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

      • julianhoewel_95's avatar
        julianhoewel_95
        Frequent Visitor
        4 years ago

        That's exactly what I was looking for. Didn't think about using variables to do this.
        Thank you very much, this helps me alot!

        Best regards to you
        Julian