Need Help with DAX!!!

[Pranjal06]

Hello, I'm new to DAX and trying to get the difference between the dollar amount in each payment cycle using DAX.
I tried to search but if someone knows the same problem with solution in other post it would be helpful if you can post the link of that solution. Thank you

[Pranjal06]

Hello  @Anonymous,

Thank you for the solution.

[ray_aramburo]

What would be the logic of the calculation? Mid-Year - Initial ?

[Pranjal06]

I want diiference in dollar.
dollar(mid-year) - dollar(Initial)
dollar(final) - dollar(midyear)

[Greg_Deckler]

@Pranjal06 So what are the expected results you are trying to achieve?

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Pranjal06]

I want this (Midyear-initial) field which is basically dollar(midyear)-dollar(initial) and same for Final-midyear.

PAYMENT SUBMISSION CYCLE	INITIAL	MID-YEAR	FINAL
PAYMENT YEAR	RAF	DOLLAR	RAF	DOLLAR	MIDYEAR-INITIAL	RAF	DOLLAR	FINAL-MIDYEAR
2022	0.225	$  565,123,214.00	0.225	$  654,644,645.00	$    89,521,431.00	0.225	$  566,588,555.00	$  477,067,124.00
2023	0.225	$  648,946,654.00	0.225	$  556,564,546.00	$  (92,382,108.00)	0.225	$  456,785,321.00	$  549,167,429.00
2024	0.225	$  321,564,542.00	0.225	$  456,456,655.00	$  134,892,113.00	0.225	$  564,789,215.00	$  429,897,102.00

[Anonymous · Accepted solution]

@Greg_Deckler @ray_aramburo Thanks for your contribution on this thread.

Hi @Pranjal06 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create 'Sort Table' and 'Sort Table2'

2. Create measures as below

MIDYEAR-INITIAL = 
VAR _year =
    SELECTEDVALUE ( 'Table'[PAYMENT YEAR] )
VAR _midyear =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[PAYMENT YEAR] = _year
                && 'Table'[Type] = "DOLLAR"
                && 'Table'[PAYMENT SUBMISSION CYCLE] = "MID-YEAR"
        )
    )
VAR _initial =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[PAYMENT YEAR] = _year
                && 'Table'[Type] = "DOLLAR"
                && 'Table'[PAYMENT SUBMISSION CYCLE] = "INITIAL"
        )
    )
RETURN
    _midyear - _initial

FINAL-MIDYEAR = 
VAR _year =
    SELECTEDVALUE ( 'Table'[PAYMENT YEAR] )
VAR _final =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[PAYMENT YEAR] = _year
                && 'Table'[Type] = "DOLLAR"
                && 'Table'[PAYMENT SUBMISSION CYCLE] = "FINAL"
        )
    )
RETURN
    _final - [MIDYEAR-INITIAL]

3. Create a matrix with the below fields settings

Toggle off the option "Text wrap" for headers and values to control the field display

Best Regards