Forum Discussion

united2win's avatar
united2win
Icon for Helper III rankHelper III
5 years ago

Running Total - DAX - Fill blanks.

Hi, I have the below tables, one is a countdistinct and one is a measure to derive the running total: In the 2nd table you can see there are two blanks that are highlighted. Ideally, I would like the measure to use the total from the previous month number if the transactions in that month is blank.

2 points: 

The Month number (column) is a "whole number" format.

The Batch Number (Rows) is a "text" format.

 

Any help will be greatly appreciated.

 

Here is the measure:

Running Total =
CALCULATE(
    DISTINCTCOUNT('VAT Registration WIP'[Id]),
    FILTER(
        ALLSELECTED('VAT Registration WIP'[BatchMonth (Docs Sent to T/O)]),
        ISONORAFTER('VAT Registration WIP'[BatchMonth (Docs Sent to T/O)], MAX('VAT Registration WIP'[BatchMonth (Docs Sent to T/O)]), DESC)
    )
)

 

 

 

 

 

Need Help
Show and Tell

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    united2win , try like

     

    Running Total =
    CALCULATE(
    DISTINCTCOUNT('VAT Registration WIP'[Id]),
    FILTER(
    ALLSELECTED('VAT Registration WIP'),
    ISONORAFTER('VAT Registration WIP'[BatchMonth (Docs Sent to T/O)], MAX('VAT Registration WIP'[BatchMonth (Docs Sent to T/O)]), DESC)
    )
    )

     

    or

     

    Running Total =
    CALCULATE(
    DISTINCTCOUNT('VAT Registration WIP'[Id]),
    FILTER(
    all('VAT Registration WIP'[BatchMonth (Docs Sent to T/O)]),
    ISONORAFTER('VAT Registration WIP'[BatchMonth (Docs Sent to T/O)], MAX('VAT Registration WIP'[BatchMonth (Docs Sent to T/O)]), DESC)
    )
    )

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, united2win 

    I guess your original table lacks the corresponding actual data, so the corresponding matrix is missing values.

    It is recommended to create two dimension tables and establish  relationships with fact tables before creating a measure.

    For example:

    Calculated table:

    DimensionTbale1_Batch Number = DISTINCT('VAT Registration WIP'[Batch Number])

    DimensionTable2_Batch Number = DISTINCT(BatchMonth (Docs Sent to T/O))

    After building relationship ,then drag these new  fields "Batch Number" and  "BatchMonth (Docs Sent to T/O)" into the matrix visual. Then when you create the corresponding measure, the matrix will not show null values.


    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.