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

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