topic Re: Dax sum cumulative by selected day for month and year in DAX Commands and Tips

Dax sum cumulative by selected day for month and year

bestevez — Tue, 10 Sep 2019 13:46:33 GMT

Hi,

I have a problem I need to solve.

I have the follow

Day	1	2	3	4	5	6	7	8	9	10	11	12	13	14
Sales	20	30	40	20	20	15	12	13	18	19	15	12	10	11

and I need to calculate the next matrix

Day	7	14
Sales	157	255

Where 157 is the cumulative sales of the first seven days and 255 is the sum of the days since 1 to 14 then i will need 1 to 21

I need to select the days for example if i select the day 8 then 1 to 8

Thanks for the help like always.

Re: Dax sum cumulative by selected day for month and year

bestevez — Tue, 10 Sep 2019 14:23:05 GMT

Hi,

i try this CALCULATE( SUM(sales); DATEMTD(dim_date[Fecha]) )

Thanks for all

Re: Dax sum cumulative by selected day for month and year

Anonymous — Wed, 11 Sep 2019 09:20:39 GMT

Below Measure can be used -

Cumulative_Sum = CALCULATE(SUM(TableName[Sales]),FILTER(ALL(TableName),TableName[DateCol] <= MAX(TableName[DateCol])))

Re: Dax sum cumulative by selected day for month and year

bestevez — Wed, 11 Sep 2019 09:35:54 GMT

Hi,

The problem is that I can't select the day and accumulate it. What makes me that measure is to give me the cumulative maximum of the month, but, if I select a day gives me the value of that day selected.

Thanks

Re: Dax sum cumulative by selected day for month and year

Anonymous — Wed, 11 Sep 2019 09:53:40 GMT

With the measure, it will show the cumulative sum upto the date selected.

Sorry I dont understand clearly what you are trying achieve. Can you provide an example.

Re: Dax sum cumulative by selected day for month and year

bestevez — Wed, 11 Sep 2019 09:59:20 GMT

HI,

for example i want to select a day 7, 14, 21 and 30 or 31 and see a cumulative sales of a month.

I got it with this measure CALCULATE( SUM( sales ); DATESMTD( date[date] ) )

Re: Dax sum cumulative by selected day for month and year

Anonymous — Wed, 11 Sep 2019 10:46:09 GMT

The measure you are using will work only for a month if you have dates for more than a month. The cumulative sum will start afresh as you are using MTD.

Try using the measure I gave, it should work for you.

Re: Dax sum cumulative by selected day for month and year

bestevez — Wed, 11 Sep 2019 10:56:54 GMT

Your measure adds a cumulative from the beginning of the data and not from what was selected.