Cumulative Sum for specific date range

[julianhoewel_95]

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

[v-easonf-msft]

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]

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 · Accepted solution]

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]

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

[amitchandak]

@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