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[Credi...
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

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