Help with date segmenters and doc balances

[rod_puente]

Hi everyone!

Pls I need your help. I have the following table in PBI desktop:

I need to know how to place a measure in DAX that allows viewing documents with balances on a certain date.

For example, in the following box I select the date until 10/30/2023, therefore, documents 448 and 774 are shown with a balance, but I need document 446 not to be shown because it must be dynamic with the date.

Regards

Rodrigo Puente

[Irwan · Accepted solution]

hello @rod_puente 

i assumed you want to remove 446 because the sum value is zero (value difference between 'Valor Doc' and 'Valor Abon').

By that assumption, please check if this accomodate your need.

1. create a new measure with following DAX.

Sum = 
var _Doc = SELECTEDVALUE('Table'[Doc])
Return
SUMX(
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Doc]=_Doc
    ),
    'Table'[Value 1]-'Table'[Value 2]
)

2. plot the measure into your table visual, then go to visual filter and filter only to show value is not zero

3. if you dont want to show the measure in table visual, lock the visual filter (shown as red circle) then remove the measure from your column table visual

Hope this will help.

Thank you.

[rod_puente]

Dear Irwan

I made the measurement in DAX that it tells me, but it doesn't sum up in the "sum" column, did I do something wrong?

Tkns, i hope your comments.

Rodrigo

[Irwan]

Hello @rod_puente 

What are 'Valor Doc' and 'Valor Abon'? Are they measures or columns?

Is those two are measures, then the DAX need to be adjusted so it will look measure values instead of column value.

Thank you.

[rod_puente]

Hi Irwan

Tkns. I checked and by mistake I had added a column instead of a measure, now I have a measure and it works fine.

I have another question, how can I make it so that document 446 does not have the 1,000,000 repeated?

I modified the original data to see this case, because I am interested in the other thing, how can I apply a sum to the "sum" column?

Regards

Rodrigo Puente

[Irwan]

Hello @rod_puente 

The DAX works like looking the value difference between 'Valor Doc' and 'Valor Abon' then it will be visually filtered out when the difference is zero and all value more than zero will be shown up.

That 1,000,000 is visible because the difference is not zero.

So what do you want by "not have the 1,000,000 repeated"? Do you want to remove it from visual or do you want to show it in single row (not shown in two rows) or any other goal/result you eant to achieve?

Thank you.

[rod_puente]

Dear @Irwan 

I understand, I want to show the 1,000,000 in a single row please.

Regards

Rodrigo P.

[Irwan]

hello @rod_puente 

the purpose of measure is just to show one row of same value between 'Doc' and 'Abon'.

i dont know what are your goal is but those have different value. 2.035.364 is num 157 while 1.053.364 is num 190.

which one do you want to remove between them? how do you want to remove them? is it by date?

please explain what you need more.

if what you need is not related to your post, please provide another sample data and show your outcome result.

Thank you.

[Anonymous]

Hi @rod_puente ,

Sorry, I'm not sure if I understood you correctly.

Do you want to filter data whose date column is less than or equal to October 30, 2023?

I found that the slicer correctly filtered the data. Document 446 is displayed because the date is less than October 30, 2023, and this is the expected behavior.

If I misunderstood the question, could you clarify it?

Best Regards,

Wearsky