Forum Discussion

Pranjal06's avatar
Pranjal06
Frequent Visitor
2 years ago
Solved

Need Help with DAX!!!

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

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    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's avatar
      Pranjal06
      Frequent Visitor
      2 years ago

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

      PAYMENT SUBMISSION CYCLEINITIALMID-YEARFINAL 
      PAYMENT YEARRAFDOLLARRAFDOLLARMIDYEAR-INITIALRAFDOLLARFINAL-MIDYEAR
      20220.225 $  565,123,214.000.225 $  654,644,645.00 $    89,521,431.000.225 $  566,588,555.00 $  477,067,124.00
      20230.225 $  648,946,654.000.225 $  556,564,546.00 $  (92,382,108.00)0.225 $  456,785,321.00 $  549,167,429.00
      20240.225 $  321,564,542.000.225 $  456,456,655.00 $  134,892,113.000.225 $  564,789,215.00 $  429,897,102.00
      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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

    • Pranjal06's avatar
      Pranjal06
      Frequent Visitor
      2 years ago

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