Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@Anonymous , Please find the pbix with date table . Table 2 ( From Table and Date)
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
@Anonymous , Please find the pbix with date table . Table 2 ( From Table and Date)
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.
@Anonymous , in file in last update. Table 2 ( From Table and Date)
@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]))
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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |