Non sequential date dimension

[VoltesDev]

Hi friends,

I have a date dimension that has non sequential value : got 1 row  with date '1/1/1900' and then start with '1/1/2010' so forth until '12/31/2025' and then 1 other row with value '12/31/2154'.. This is actually because I get it from ERP system whereby the min max date of the SQL DB

Is it true by having this, espeically with '1/1/1900' then we cannot calculate Cumulative using DAX ?

I create formula like this :

CALCULATE(
           SUM('Inventory'[value]),
              FILTER(ALL (Dates[Date]),
              Dates[Date] <= MAX(Dates[Date])
        )

I have some rows with dates of "1/1/1900" and the rest rows are with their original date respectively. 

When I use the DAX formula, let say in table with Date in it s rows or months,  it is givin gme some weird value. Is it because that skip date ?

Thanks

[v-janeyg-msft]

Hi, @VoltesDev 

Do you mean that you don't want to sum 1/1/1900's data?

If yes, you can try:

CALCULATE(
           SUM('Inventory'[value]),
              FILTER(ALL (Dates[Date]),
              Dates[Date] <= MAX(Dates[Date])&&Dates[Date] > date(1900,1,1)
        )

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[amitchandak]

@VoltesDev , I would suggest moving all 1900 date to 2010 or make then null in power query and try

[VoltesDev]

Hi,

I think I can't just change the date (move) since I'm using Direct Query, is there any other trick ?

One of the reason is because in transaction, they do have transaction date of '01/01/1900'

or should I use other DAX formula ?

Thanks

[amitchandak]

@VoltesDev , Table dates contain continuous dates from 2010 onward and are marked as date table ?

I think you should get one blank record and the rest should work

[VoltesDev]

Hi,

No, the date dimension is with '1/1/1900' for the 1st row, and then start with '1/1/2010' onward until 2025.

I'm sorry, I didn't get what do you mean by blank record.

Thanks

[v-janeyg-msft]

Hi, @VoltesDev 

I couldn't get your reply. I don't know your specific situation.

If you have solved the problem, welcome to share your thoughts.

If you still need help, please feel free to ask me.

Best Regards,
Community Support Team _ Janey