Forum Discussion

carlottaf's avatar
carlottaf
Frequent Visitor
5 years ago
Solved

DATEADD using a month not a date

Hi!

I have a table that has only months and not a complete date. For example instead of having 20210708, I have 202107 (month).
This table is connected to the calendar using the month column.

I am trying to sum a column af this table for the last 12 and 24 months. So take the last 12 or 24 months and then sum the column to obtain the total value of the last 112 or 24 months.
I tried with this formula: 

 

 CALCULATE(DATEADD(LASTNONBLANK('Calendar'[Date] ,SUM(GiacenzaMagazzini[ValoreGiacenza])) ,-12 ,MONTH))

 

The problem is that my table that is called GiacenzaMagazzini does not contain a data column and it is filtered by calendar using the month column.

Is there a way to do it using the month coulmn I have in my calendar?

Thanks,
Carlotta.
Need Help
Tips and Tricks
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    Hi carlottaf ，

    Try the following steps:

    Step 1，new column on  GiacenzaMagazzini:

    date = DATE(LEFT(GiacenzaMagazzini[month],4),RIGHT(GiacenzaMagazzini[month],2),1)

     

    Step 2,Use the following dax to create a new measure:

    final = 
CALCULATE (
    SUM ( 'GiacenzaMagazzini'[ValoreGiacenza]),
    DATESBETWEEN (
        'Calendar'[Date],
        CALCULATE(DATEADD(LASTNONBLANK('Calendar'[Date] ,SUM(GiacenzaMagazzini[ValoreGiacenza])) ,-12 ,MONTH)),
        LASTDATE ( 'Calendar'[Date] )
    )
)

    Final get:

     

     

    Wish it is helpful for you!

     

    You could download my pbix flie if you need!

     

    Best Regards

    Lucien

3 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago
    DATEADD(<dates>,<number_of_intervals>,<interval>)

    You use either the name of a column containing dates or a one column table containing dates.

    • carlottaf's avatar
      carlottaf
      Frequent Visitor
      5 years ago

      So if I have no column containing dates I can't use this formula, correct?

      Can I use something else?

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi carlottaf ，

    Try the following steps:

    Step 1，new column on  GiacenzaMagazzini:

    date = DATE(LEFT(GiacenzaMagazzini[month],4),RIGHT(GiacenzaMagazzini[month],2),1)

     

    Step 2,Use the following dax to create a new measure:

    final = 
CALCULATE (
    SUM ( 'GiacenzaMagazzini'[ValoreGiacenza]),
    DATESBETWEEN (
        'Calendar'[Date],
        CALCULATE(DATEADD(LASTNONBLANK('Calendar'[Date] ,SUM(GiacenzaMagazzini[ValoreGiacenza])) ,-12 ,MONTH)),
        LASTDATE ( 'Calendar'[Date] )
    )
)

    Final get:

     

     

    Wish it is helpful for you!

     

    You could download my pbix flie if you need!

     

    Best Regards

    Lucien