Forum Discussion
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 _ResTableHello guys
Thank you for your help. @AIB thank you, worked perfectly, thanks for the help.
Regards, Portugal.
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
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
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
_ResTableThanks @ AIB
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_1 hora_1 valor_1 moeda_1 dataInt_1 datahora_1 01/01/2020 93412
0,89 G 20200101 2020010193412 01/01/2020 122402 0,78 G 20200101 20200101122402 01/01/2020 81432 111 C 20200101 2020010181432 01/01/2020 162341 109 C 20200101 20200101162341 01/01/2020 173245 0,92 G
20200101 20200101173245 01/01/2020 102341 453 AO 20200101 20200101102341 01/01/2020 151909 390 AO 20200101 20200101151909 02/01/2020 120405 115 C 20200102 20200102120405 02/01/2020 134232 116 C 20200102 20200102134232 02/01/2020 120923 438 AO 20200102 20200102120923 02/01/2020 84323 0,79 G 20200102 2020010284323 02/01/2020 94500 0,84 G 20200102 2020010294500 02/01/2020 172321 110,45 C 20200102 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
