Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
julianomachado4
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.

julianomachado4_0-1626976787920.png

 

5 REPLIES 5
julianomachado4
Regular Visitor

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

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.
Appreciate your Kudos!!
Connect on Linkedin

Samarth_18
Community Champion
Community Champion

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,

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Oi @Samarth_18 , alright?

 

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

julianomachado4_0-1627314881159.png

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


julianomachado4_1-1627315138029.png

 

 

 

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:-

Samarth_18_0-1627364200157.png

Test case 1 with 31 dec as selected date:-

Samarth_18_1-1627364227935.png

 

Test case 2 with 30 dec as selected date

Samarth_18_2-1627364260918.png

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.
Appreciate your Kudos!!
Connect on Linkedin

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors