cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## Calculation of retroactive values ​​using DAX

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.

5 REPLIES 5
Regular Visitor

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

Community Champion

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

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Community Champion

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,

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Regular Visitor

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

Community Champion

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

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors