Calculation of retroactive values ​​using DAX

[julianomachado4]

Hi,

I'm new to the field, and I'm in need of some help. It can be a simple question. I need to calculate the total amount of retroactive card sales, like for example.

If I select the day 12/31/2020 they must list (add up) the sales that issued <= 12/31/2020 in which the retirement date is greater than 12/31/2020 or is blank.

[julianomachado4]

It was right, thank you very much for your help and sorry for the delay in replying.

[Samarth_18]

Thats greats, @julianomachado4! Kindly marked this answer as a solution so that it will helps other to find it more quickly.

[Samarth_18]

Hi @julianomachado4 ,

You can try below code:-

Medida =
VAR _selected_date =
    SELECTEDVALUE ( CARTOES[DATAVENDA] )
RETURN
    CALCULATE (
        SUM ( CARTOES[VALOR] ),
        CARTOES[DATAVENDA] <= _selected_date
            && (
                CARTOES[retirement_date] >= _selected_date
                    || ISBLANK ( CARTOES[retirement_date] )
            )
    )

It would be more helpful if you could share some sample data and snapshot of expected output. Kindly share if above code not help you.

Thanks,

[julianomachado4]

Oi @Samarth_18 , alright?

Thanks for the answer, I'll detail how the expected output should be:

In this small table, if the sale date (column 4) I select is, for example, 12/31/2020, the expected result should be: $50.00. Because the day 01/04/2021 is greater than 12/31/2020... or if it is blank, it should also bring the same $50.00.

In this image, if I selected the sale date = 12/30/2020 the expected result would be $ 470,07

[Samarth_18]

Hi @julianomachado4 

Below code will help to get your desired result.

_Medida = 
VAR _selected_date =
    SELECTEDVALUE ( _CARTOES[DATAVENDA] )
RETURN
    CALCULATE (
        SUM ( _CARTOES[VALOR] ),
        _CARTOES[DATAVENDA] <= _selected_date
            && (
                _CARTOES[DATABAIXA] > _selected_date
                    || ISBLANK ( _CARTOES[DATABAIXA] )
            )
    )

My data table:-

Test case 1 with 31 dec as selected date:-

Test case 2 with 30 dec as selected date

Thanks,

Samarth