Forum Discussion

NishPatel's avatar
NishPatel
Resolver II
3 years ago
Solved

Dax Measure

I need help with a measure to get result as shown below in column Balance.  If there is no value in Credit column then I dont want to show YTD balance. The measure that I have is 

 

IF(Table1[Credit]=BLANK(),BLANK(),Table1[Debit]-Table2[Credit])

 

which is giving me blanks where the value is 0. 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    HI NishPatel,

    You can try to use the following measure formula if it suitable for your requirement:

    VarianceMeasure =
VAR currDate =
    MAX ( DebitTable[Date] )
RETURN
    IF (
        [CreditMeasure] = BLANK (),
        SUMX (
            SUMMARIZE (
                FILTER ( ALLSELECTED ( DebitTable ), [Date] <= currDate ),
                [Month],
                [Date],
                "Debit", SUM ( DebitTable[Amount] ),
                "Credit",
                    CALCULATE (
                        SUM ( CreditTable[Amount] ),
                        FILTER (
                            ALLSELECTED ( CreditTable ),
                            CreditTable[Date] IN VALUES ( DebitTable[Date] )
                        )
                    )
            ),
            [Debit] - [Credit]
        ),
        [DebitMeasure] - [CreditMeasure]
    )

    Regards,

    Xiaoxin Sheng

3 Replies

  • Mikelytics's avatar
    Mikelytics
    Resident Rockstar
    3 years ago

    Hi NishPatel 

     

    I think the general problem is that you work with two different tables and want to build a measure without aggregation functions.

     

    Can you please share your data model with a picture as well as the structure of table 1 and table 2. Do you use an additional date table?

     

    Best regards

    Michael

    • NishPatel's avatar
      NishPatel
      Resolver II
      3 years ago

      Hi Mike, First of all thank you for taking your time to help me out here. Because of the confidentiality of the data, I am unable to share my original table/pbix file. But below are the sample data tables that I have created which are almost similar to my data. Month Table is join by month with other two tables. And I have below 3 measure as well.

       

       

      DebitMeasure = CALCULATE(SUM(DebitTable[Amount]),SUMMARIZE(DebitTable, DebitTable [Month]))

      CreditMeasure = CALCULATE(SUM(CreditTable[Amount]),SUMMARIZE(CreditTable, DebitTable [Month]))

      VarianceMeasure =  IF(CreditTable[CreditMeasure]=BLANK(),BLANK(),DebitTable[DebitMeasure])-CreditTable[CreditMeasure])

       

      Thanks again.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        HI NishPatel,

        You can try to use the following measure formula if it suitable for your requirement:

        VarianceMeasure =
VAR currDate =
    MAX ( DebitTable[Date] )
RETURN
    IF (
        [CreditMeasure] = BLANK (),
        SUMX (
            SUMMARIZE (
                FILTER ( ALLSELECTED ( DebitTable ), [Date] <= currDate ),
                [Month],
                [Date],
                "Debit", SUM ( DebitTable[Amount] ),
                "Credit",
                    CALCULATE (
                        SUM ( CreditTable[Amount] ),
                        FILTER (
                            ALLSELECTED ( CreditTable ),
                            CreditTable[Date] IN VALUES ( DebitTable[Date] )
                        )
                    )
            ),
            [Debit] - [Credit]
        ),
        [DebitMeasure] - [CreditMeasure]
    )

        Regards,

        Xiaoxin Sheng