Forum Discussion

esilva32's avatar
esilva32
Frequent Visitor
7 years ago
Solved

DAX Calculation

Hello guys

I am performing a calculation with DAX on power BI and would like to know if anyone could help me. My data is in table A and the results I want are in table B.

Best regards,
Thank you

Need Help
  • AlB's avatar
    AlB
    7 years ago

    Hi esilva32

     

    It certainly looks like it would be easier to do this in the query editor. If you do need to do it in DAX, try this. Create a new calculated table:

     

    TableB = 
VAR _BaseTable =
    ADDCOLUMNS (
        GENERATE (
            DISTINCT ( TableA[Id] );
            GENERATESERIES (
                CALCULATE ( DISTINCT ( TableA[StartDate] ) );
                CALCULATE ( DISTINCT ( TableA[EndDate] ) )
            )
        );
        "TempVal"; CALCULATE ( DISTINCT ( TableA[Value] ) )
    )
VAR _Dates =
    DISTINCT ( SELECTCOLUMNS ( _BaseTable; "Date"; [Value] ) )
VAR _ResTable =
    ADDCOLUMNS (
        _Dates;
        "Total Value"; SUMX ( _BaseTable; IF ( [Value] = [Date]; [TempVal] ) )
    )
RETURN
    _ResTable

     

     

     

  • esilva32's avatar
    esilva32
    7 years ago

    Hello guys

    Thank you for your help. @AIB thank you, worked perfectly, thanks for the help.

    Regards, Portugal.

  • AlB's avatar
    AlB
    7 years ago

    esilva32

     

    Minor modification:

     

    TableB =
VAR _BaseTable =
    ADDCOLUMNS (
        GENERATE (
            DISTINCT ( TableA[Id] );
            GENERATESERIES (
                CALCULATE ( DISTINCT ( TableA[StartDate] ) );
                MIN ( TODAY (); CALCULATE ( DISTINCT ( TableA[EndDate] ) ) )
            )
        );
        "TempVal"; CALCULATE ( DISTINCT ( TableA[Value] ) )
    )
VAR _Dates =
    DISTINCT ( SELECTCOLUMNS ( _BaseTable; "Date"; [Value] ) )
VAR _ResTable =
    ADDCOLUMNS (
        _Dates;
        "Total Value"; SUMX ( _BaseTable; IF ( [Value] = [Date]; [TempVal] ) )
    )
RETURN
    _ResTable

15 Replies

  • jthomson's avatar
    jthomson
    Solution Sage
    7 years ago

    You want Power Query for this - open the advanced editor and stick this in:

     

    = Table.AddColumn(#"Changed Type", "NewDate", each List.Dates([start], Duration.TotalDays([end]-[start])+1, #duration(1,0,0,0)))

     

    Making sure the columns are the right names. Then expand out the column into new rows, make the new column a date, and you can just sum on your value column and you're done

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    You can expand out your rows in Power Query using something like:

     

        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Dates( [Initial Date], Number.From( [Final Date]- [Initial Date]) +1, #duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")

     

     

    There is also GENERATESERIES in DAX. But, depending on what you want to do, check out these two Quick Measures as I think you want something like them.

    https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
    https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

    • AlB's avatar
      AlB
      Community Champion
      7 years ago

      Hi esilva32

       

      It certainly looks like it would be easier to do this in the query editor. If you do need to do it in DAX, try this. Create a new calculated table:

       

      TableB = 
VAR _BaseTable =
    ADDCOLUMNS (
        GENERATE (
            DISTINCT ( TableA[Id] );
            GENERATESERIES (
                CALCULATE ( DISTINCT ( TableA[StartDate] ) );
                CALCULATE ( DISTINCT ( TableA[EndDate] ) )
            )
        );
        "TempVal"; CALCULATE ( DISTINCT ( TableA[Value] ) )
    )
VAR _Dates =
    DISTINCT ( SELECTCOLUMNS ( _BaseTable; "Date"; [Value] ) )
VAR _ResTable =
    ADDCOLUMNS (
        _Dates;
        "Total Value"; SUMX ( _BaseTable; IF ( [Value] = [Date]; [TempVal] ) )
    )
RETURN
    _ResTable

       

       

       

      • esilva32's avatar
        esilva32
        Frequent Visitor
        7 years ago

        Hello guys

        Thank you for your help. @AIB I tried your code and the message was this: it is not possible to convert multiple columns to a scalar value.

        Guys, in Power Query I have to build a new table to put this code in it?