Cumulative with non continues date

DAX DAX Calculations
1

Hi,

If I have transaction data which the Date dimension related to it having skip date, how to make it Cumulative, from the beginning of the date and transaction date ?

So I have date dimension with 1 value of '‘1/1/1900’ and jump to '‘1/1/2010’ then continues.
I have some rows with the date ‘1/1/1900’ beside those with correct date.
When using this formula :

  Cumulative inventory value = CALCULATE(
                                SUM('Inventory-Transactions'[Inventory value]),
                                DATESYTD(Dates[Date])
                            )

I got the result with the date Jan 1900 total and is not continued like this :
image

Need help how to resolve this ?
Btw I’m using Direct Query, so there is some limitation on Power query changes.

Thanks

2

Hi @Toni ,

So you’d like it to be a cumulative from the Jan 1900 record to Dec 2020?

I believe it’s this part that is tripping it up:

DATESYTD(Dates[Date])

This will do the running total within each year. If you switched it to this:

Cumulative inventory value =
CALCULATE(
SUM(‘Inventory-Transactions’[Inventory value]),
FILTER(ALLSELECTED(Dates),
Dates[Date] <= MAX(Dates[Date]))
)

That should work from my bit of testing.

1 Like
3

Hi,

Thanks, it is solved. But I have to use ALL instead ALLSELECTED, it’s because I’m gonna use it in Area Chart with axis month, but I didn’t tell you in the first place.

Many thanks for the help.