topic Re: Create a new table with colmuns filtered by dates in DAX Commands and Tips

Create a new table with colmuns filtered by dates

Anonymous — Tue, 01 Nov 2022 19:44:32 GMT

Hellow everyone. I am working on a new table that is built from a database that includes this columns:

Clients, Accounts, Dates (from month 1 to month12) and Values

And I need to organice in a separate table per Clients and Accounts the values from moth 1, next the values from moth 2 andso on.

I tried with the next code but I find ount that the GENERATE function do not allow dates in the filters and I also cannot add the Accounts column next to the Clients column.

Table_1 =

GENERATE (

VALUES ( Table[Clients] ),

VALUES ( Table[Accounts] ),

VAR Month1 =

CALCULATE ( SUM ( Table[Value] ), Table[Month] = "31-01-2022" )

VAR Month2 =

CALCULATE ( SUM ( Table[Value] ), Table[Month] = "28-02-2022")

.....

RETURN

ROW (

"Month_1", Month1, "Month_2", Month2

)

Thanks in advance!!!

Re: Create a new table with colmuns filtered by dates

amitchandak — Wed, 02 Nov 2022 02:25:15 GMT

Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Check if this can help

https://amitchandak.medium.com/power-bi-power-query-vs-dax-months-between-range-df019cec823b

Re: Create a new table with colmuns filtered by dates

Anonymous — Thu, 03 Nov 2022 15:05:38 GMT

Sure! In the next picture I try to show the initial and desired table:

Re: Create a new table with colmuns filtered by dates

v-yadongf-msft — Mon, 07 Nov 2022 09:20:54 GMT

Hi Anonymous

This is my test table:

Please transform data in Power Query: Select Date and Value column - Pivot column.

Select OK:

You will get a table like this, I think this is the expected table you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.