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 

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