topic Re: Calculation of retroactive values using DAX in DAX Commands and Tips

Calculation of retroactive values using DAX

julianomachado4 — Thu, 22 Jul 2021 17:59:59 GMT

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.

Re: Calculation of retroactive values using DAX

Samarth_18 — Fri, 23 Jul 2021 05:47:06 GMT

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,

Re: Calculation of retroactive values using DAX

julianomachado4 — Mon, 26 Jul 2021 16:02:25 GMT

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

Re: Calculation of retroactive values using DAX

Samarth_18 — Tue, 27 Jul 2021 05:38:38 GMT

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

Re: Calculation of retroactive values using DAX

julianomachado4 — Wed, 11 Aug 2021 12:26:44 GMT

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

Re: Calculation of retroactive values using DAX

Samarth_18 — Thu, 12 Aug 2021 07:53:18 GMT

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

topic Re: Calculation of retroactive values ​​using DAX in DAX Commands and Tips

Calculation of retroactive values ​​using DAX

Re: Calculation of retroactive values ​​using DAX

Re: Calculation of retroactive values ​​using DAX

Re: Calculation of retroactive values ​​using DAX

Re: Calculation of retroactive values ​​using DAX

Re: Calculation of retroactive values ​​using DAX

topic Re: Calculation of retroactive values using DAX in DAX Commands and Tips

Calculation of retroactive values using DAX

Re: Calculation of retroactive values using DAX

Re: Calculation of retroactive values using DAX

Re: Calculation of retroactive values using DAX

Re: Calculation of retroactive values using DAX

Re: Calculation of retroactive values using DAX