How to split the table and create a new table based on column value without using filter

[Manjarigoyal1]

I have a table with a date and site. Each week there will be new sites with that week's date. I need to split this table by Data date column so that I have a new table for each data date. Then I need to merge the tables by Site and then compare previous week to next week and create a column with values existing/drop and New. it will be rolling calulation for each week. Is there any other way to do it. Below is the screenshot of demo data.

Input File: 

Output File:

[amitchandak · Accepted solution]

@Manjarigoyal1 , Not very clear , why you need break table for this week vs last week

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

Refer if this can help

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[v-xiaotang]

Hi @Manjarigoyal1 

Thanks for reaching out to us.

>> I need to split this table by Data date column so that I have a new table for each data date. Then I need to merge the tables by Site and then compare previous week to next week and create a column with values existing/drop and New. 

Is there only two columns date and site in the input file? Because in the output file, the relationship between the various columns is not obvious, so it is not clear from the picture how to split the columns in the input file.

Best Regards,

Community Support Team _Tang

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

[Manjarigoyal1]

There are only two columns in the original table Site and Data date. the new dates will be added each week to the table.

[v-xiaotang · Accepted solution]

Hi @Manjarigoyal1 

Thanks for your reply.

>>  to split the table and create a new table based on column value without using filter

usually, we use function such like 

SELECTCOLUMNS function (DAX) - DAX | Microsoft Docs

SUMMARIZECOLUMNS function (DAX) - DAX | Microsoft Docs

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

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

[amitchandak · Accepted solution]

@Manjarigoyal1 , Not very clear , why you need break table for this week vs last week

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

Refer if this can help

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[Manjarigoyal1]

I need to see the history/trend week by week that how many sites are dropped from last week to this week, how many sites are still there from last week and how many sites are added this week.