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-02-01	15
2020-03-01	18
2020-04-01	20

All the measures that I want to examine on a monthly basis can then be added to each column of this table.

How can I do such export into a new table?

Thanks.

[Anonymous]

Hi @Anonymous,

You can use summarize function to aggregate table values based on specific categories, then you can get a new table that summer raw value based on raw category.

Then you can mapping the new table and raw table field based on the category, raw table measure expressions can use with new table fields and calculated based on the relationship mapping.

Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

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

[Anonymous]

@amitchandak 

Thanks a lot. 

That works perfectly with the simple SUM measure.

In my own project, I am working with more complicated DAX. For example, I want to measure cumulative sum for each month. That is not working properly.

Do you have any idea how to do that?

Thanks.

[amitchandak]

@Anonymous , in file in last update. Table 2 ( From Table and Date) 

[amitchandak]

@Anonymous , Try new table

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

or

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

[Anonymous]

Thanks @amitchandak 

Unfortunately, you dax for creating new table are actually not working.

So, I have to use this to create a new table. Right? Can you please provide some explanations with your solution?!

Also, can you please test them, preferrably in a power bi file?!

I also have to add that I have another table "Calendar" that has already the column "Start of Month". So, I can probably use it, but I don't know how exactly. I tried it in your equations above, but did not work.

Thanks.

[amitchandak]

@Anonymous , I have actually posted the revised answer, after testing, This option was working perfectly 

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

Two tables one after another. I will check one with date table. That should work with only summarize. if tables are joined