cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
esilva32 Frequent Visitor
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

Accepted Solutions
Super User
Super User

Re: DAX Calculation

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

Highlighted
esilva32 Frequent Visitor
Frequent Visitor

Re: DAX Calculation

Hello guys

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

Regards, Portugal.

View solution in original post

Super User
Super User

Re: DAX Calculation

@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

9 REPLIES 9
jthomson New Contributor
New Contributor

Re: DAX Calculation

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

Super User
Super User

Re: DAX Calculation

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User
Super User

Re: DAX Calculation

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

Re: DAX Calculation

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?

Super User
Super User

Re: DAX Calculation

@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).

Highlighted
esilva32 Frequent Visitor
Frequent Visitor

Re: DAX Calculation

Hello guys

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

Regards, Portugal.

View solution in original post

esilva32 Frequent Visitor
Frequent Visitor

Re: DAX Calculation

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

 

Super User
Super User

Re: DAX Calculation

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

Re: DAX Calculation

Thanks @ AIB

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 4,195 guests
Please welcome our newest community members: