Forum Discussion

rod_puente's avatar
rod_puente
Icon for Helper I rankHelper I
2 years ago
Solved

Help with date segmenters and doc balances

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

 

 

 

 

Need Help
  • Irwan's avatar
    Irwan
    2 years ago

    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.

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

  • Irwan's avatar
    Irwan
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      rod_puente
      Icon for Helper I rankHelper I
      2 years ago

      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's avatar
        Irwan
        Icon for Super User rankSuper User
        2 years ago

        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.