Export the result of dax as a new table

[Anonymous]

Hi 

I have some data and I have built many DAX measures for different analysis on them. I want to export the results of my measures into a new table in a monthly basis. The following is a simple working example:

raw data:

date	sale
2020-01-15	1
2020-01-19	5
2020-01-21	2
2020-01-29	4
2020-02-01	2
2020-02-04	2
2020-02-06	3
2020-02-14	5
2020-02-19	1
2020-02-25	2
2020-03-01	6
2020-03-05	1
2020-03-10	4
2020-03-20	5
2020-03-25	2
2020-04-01	3
2020-04-10	2
2020-04-15	4
2020-04-19	2
2020-04-25	9

Now, imagine I have a DAX measure to sum sale per month (SUM(raw[sale]). I want to output the result of this measure into a new table and each row represents one month (for example start of month is good for me):

output table:

start of month	sum of sale
2020-01-01	12
2020-01-02	15
2020-01-03	18
2020-01-04	20

How can I do such export into a new table?

Thanks.

[v-deddai1-msft]

Hi @Anonymous ,

You can use the following calculated table based on your sample data:

table2 = SUMMARIZE(SELECTCOLUMNS('Table',"start of month",DATE(YEAR('Table'[date]),MONTH('Table'[date]),1)),[start of month],"sum of sales",CALCULATE(SUM('Table'[sale]), FILTER('Table',MONTH('Table'[date])= MONTH([start of month])&&YEAR('Table'[date])=YEAR([start of month]))))

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

Best Regards,

Dedmon Dai

[amitchandak · Accepted solution]

@Anonymous , pbix with date table  . Table 2 ( From Table and Date) . Table 2 ( From Table and Date) 

[amitchandak]

@Anonymous , New table

SUMMARIZE(SELECTCOLUMNS('Table',"Month" , EOMONTH('Table'[date],-1)+1 , "Sales",SUM('Table'[sale])),[Month],"sales",SUM('Table'[sale]))

Try two table

Table 3 = SELECTCOLUMNS('Table',"Month", EOMONTH([date],-1)+1, "sale",'Table'[sale])

Table 4 = SUMMARIZE('Table 3','Table 3'[Month],"sales",SUM('Table 3'[sale]))