Create a new table with colmuns filtered by dates

[Anonymous]

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!!!

[amitchandak]

@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

[Anonymous]

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

[v-yadongf-msft · Accepted solution]

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.