Forum Discussion

VoltesDev's avatar
VoltesDev
Helper V
4 years ago

Non sequential date dimension

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

 

 

 

 

Need Help
Show and Tell

6 Replies

    • VoltesDev's avatar
      VoltesDev
      Helper V
      4 years ago

      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's avatar
        amitchandak
        Super User
        4 years ago

        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

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    4 years ago

    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