sum of amount corresponding to date range

[Anonymous]

Hi All,

Below is my sample data,

Expected output: ( date filter 01-02-2020 to 31-03-2020)

Assume that, I have data upto 30-04-2020.

I need to calculate the last 3 columns (as measures) and want to show the sum of sales amount where 'End total'=1.

Problem is that, I don't have date value, where 'End total'=1. This row is just to show the sum.

1) sum amount (selected range) - sum of sales amount between selected date range in the date filter.

2) sum amount (before selected range) - sum of sales amount, date ranges from begining (in this ex 01-01-2020) to the max selected date (To date in date filter).

3) sum amount (upto last date) - sum of sales amount which is not affected by date filter, that includes all date (ie from begining to ending of data).

Thanks in advance.......

[littlemojopuppy · Accepted solution]

Hi @Anonymous 

Try these measures

Total Sales = SUM(Sales[Sales Amount])

Sales in Date Range = 
    IF(
        ISFILTERED(Sales[Date]),
        BLANK(),
        CALCULATE(
            [Total Sales],
            VALUES('Calendar'[Date])
        )
    )

Sales Before Date Range = 
    VAR MaximumDate =
        MAXX(
            VALUES('Calendar'[Date]),
            'Calendar'[Date]
        )
    RETURN

    IF(
        ISFILTERED(Sales[Date]),
        BLANK(),
        CALCULATE(
            [Total Sales],
            'Calendar'[Date] <= MaximumDate
        )
    )

All Sales = 
    IF(
        ISFILTERED(Sales[Date]),
        BLANK(),
        CALCULATE(
            [Total Sales],
            ALL('Calendar'[Date])
        )
    )

Here's the results

Hope this helps!  🙂

[littlemojopuppy]

My solution assumes you have a date table and it is marked accordingly.