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
esilva32's avatar
esilva32
Frequent Visitor
6 years ago

Hello people.

I wonder if anyone here has possibilities to help me in PBI.

I need from table A with the fields date_1, time_1, value_1, currency_1, dataInt_1 and datahora_1, create a new table B that is a grouping by date_1, currency_1 and the maximum of datahora_1 and then bring the value_1 that corresponds to each one of these lines

 

thanks

 

data_1hora_1valor_1moeda_1dataInt_1datahora_1
01/01/2020

93412

0,89G202001012020010193412
01/01/20201224020,78G2020010120200101122402
01/01/202081432111C202001012020010181432
01/01/2020162341109C2020010120200101162341
01/01/20201732450,92

G

2020010120200101173245
01/01/2020102341453AO2020010120200101102341
01/01/2020151909390AO2020010120200101151909
02/01/2020120405115C2020010220200102120405
02/01/2020134232116C2020010220200102134232
02/01/2020120923438AO2020010220200102120923
02/01/2020843230,79G202001022020010284323
02/01/2020945000,84G202001022020010294500
02/01/2020172321110,45C20200102 20200102172321
esilva32's avatar
esilva32
Frequent Visitor
6 years ago

Hello people.

I wonder if anyone here has possibilities to help me in PBI.

I need from table A with the fields data_1, time_1, valor_1, moeda_1, dataInt_1 and datahora_1, create a new table B that is a grouping by date_1, moeda_1 and the maximum of datahora_1 and then bring the valor_1 that corresponds to each one of these lines

  • AlB's avatar
    AlB
    Community Champion
    6 years ago

    Hi esilva32

    To make the requirement a bit clearer,  can you please show what the expected resulting table  B would be? BAsed on the table A example you provide

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Cheers  Datanaut

  • esilva32's avatar
    esilva32
    Frequent Visitor
    6 years ago

    Hello!
    The possible result would be what is in the image below.

     

     

    I need to take only a few fields where the field datahora_

     

    1 is maximum.

    Thanks

  • AlB's avatar
    AlB
    Community Champion
    6 years ago

    Hi esilva32 

    What value do you want for each  date-Currency pair, the latest one (time)?

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Cheers  Datanaut

  • esilva32's avatar
    esilva32
    Frequent Visitor
    6 years ago

    Hi AlB 

     

    Yes, for each date and currency I just need to get the amount corresponding to the date and the maximum time of that date for that currency.

     

    best regards

     

    Thank you