Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NishPatel
Resolver II
Resolver II

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. 

 

 

NishPatel_0-1670258849420.png

 

1 ACCEPTED SOLUTION

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
Mikelytics
Resident Rockstar
Resident Rockstar

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

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

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.

 

NishPatel_0-1670261405751.png

 

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.

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.