topic DAX Create table with each day of a month and accumulated sales for each day in DAX Commands and Tips

DAX Create table with each day of a month and accumulated sales for each day

Anonymous — Fri, 20 Nov 2020 15:36:43 GMT

Hello,

From a simple fact table of sales which shows the date of the sale and the amount ($) sold, connected to a Calendar table, I'm trying to find some function or group of functions that return a 2 column table according to a month defined in the function: Day of the month | Accumulated Sale

The accumulated sale to each of those days of the month, would also need to consider the sales of previous months.

I've tried using SUMMARIZE per day, and an acumulative function with CALCULATE and ALL, but I think it is not working because of the way SUMMARIZE creates the filter context.

Thank you very much,

Jesus

Re: DAX Create table with each day of a month and accumulated sales for each day

Anonymous — Fri, 20 Nov 2020 16:15:29 GMT

Not clear what you're trying to do. Are you trying to create a calculated table in the model? A measure can't return a table, so it can't be a measure.

Re: DAX Create table with each day of a month and accumulated sales for each day

Anonymous — Fri, 20 Nov 2020 16:17:51 GMT

Yes, I want to create a calculated table in the model, or most probably I would use it as a virtual table in a measure.

Re: DAX Create table with each day of a month and accumulated sales for each day

amitchandak — Sat, 21 Nov 2020 01:53:21 GMT

Anonymous , Sample data would have helped. With help from a date table

example

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[date])))

Without date table

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(Sales),Sales[Sales Date] <=max(Sales[Sales Date])))