cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
esilva32
Frequent Visitor

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 youCapturar.PNGCapturar.PNG

3 ACCEPTED SOLUTIONS

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

 

 

 

View solution in original post

esilva32
Frequent Visitor

Hello guys

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

Regards, Portugal.

View solution in original post

@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

View solution in original post

15 REPLIES 15
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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

 

 

 

View solution in original post

esilva32
Frequent Visitor

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?

@esilva32

 

It works fine on my end. Check it out in this file. Maybe you've done something differently.

 

On a different note, please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and run a quick test, plus it increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

esilva32
Frequent Visitor

Hello @AIB thank you.

If I want to repeat each line until the end date or for those that the end date is higher than today's date, repeat until today's date, what should I change in the code?

I sent an example in a pbix file where I want the result of table B from table A.

Could you help me again? I apologize for the inconvenience

 

thanks,

 

http://s000.tinyupload.com/?file_id=66436319019309661221

 

@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

View solution in original post

esilva32
Frequent Visitor

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

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

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
Frequent Visitor

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

 

 

I need to take only a few fields where the field datahoraVBV.PNG_

 

1 is maximum.

Thanks

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
Frequent Visitor

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

esilva32
Frequent Visitor

Thanks @ AIB

 

esilva32
Frequent Visitor

Hello guys

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

Regards, Portugal.

View solution in original post

jthomson
Solution Sage
Solution Sage

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.