Export the result of the dax as a new table

[Syndicate_Admin]

Hello

I have some data and have built many DAX measures for different analyses on them. I want to export the results of my measurements to a new table monthly. The following is an example of a simple job:

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 that I have a DAX measure to add the sale per month (SUM(raw[sale]). I want to generate the result of this measure in a new table and each row represents one month (for example, the start of the month is good for me):

output table:

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

How do I do that export in a new table?

Thank you.

[Anonymous]

Hi @Syndicate_Admin ,

Please try to use the following formula to create a new table:

New Table =
ADDCOLUMNS (
    DISTINCT (
        SELECTCOLUMNS (
            'Table',
            "start of month", DATE ( [Date].[Year], [Date].[MonthNo], 1 )
        )
    ),
    "sum of sale",
        VAR _mon =
            MONTH ( [start of month] )
        RETURN
            CALCULATE (
                SUM ( 'Table'[Sale] ),
                FILTER ( 'Table', 'Table'[Date].[MonthNo] = _mon )
            )
)

The final output is shown below:

Here is the pbix file.

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

[amitchandak]

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

[amitchandak · Accepted solution]

@Syndicate_Admin ,

Try two table , Table 4 you need

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]))