Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
3 years ago
Solved

Limit the cumulative sum using DAX

Hello Friends, I am having a problem when creating a sum calculated using DAX and I can not find the solution.

I have the following set of data, where, on the one hand, I have the budget for a certain number of periods and on the other the expenditure that has been executed.

I have been asked to display on a line chart the Accumulated budget and expense, which I have achieved with the following code.

And the result is this:
I would like the curve of accumulated expenditure (blue line) to only be drawn until the month we have data and not for all periods.
I will appreciate any help or lights you can give me.
  • Syndicate_Admin's avatar
    Syndicate_Admin
    3 years ago

    Hi @Syndicate_Admin, thanks for the answer, it is a partial help to the problem I have, but as we see a straight line is still drawn for the current month, 202211.
    At the moment I have overcome it in this way: 

    gasto acum = 
VAR fecha_resta_mes = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) )
VAR Result = 
IF(
  MAX('Table'[periodo])<=CONVERT(FORMAT(fecha_resta_mes,"yyyymm"),INTEGER),
  CALCULATE(
    SUM('Table'[gasto]),
    FILTER(
      ALLSELECTED('Table'),[periodo]<=MAX('Table'[periodo])
    )
  )
)
RETURN
    Result

    Obtained this result:


    I would like to know if anyone has any better solution, since for the moment I would be leaving it at that.
    Thank you.

2 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, Syndicate_Admin ;

    May be could try this measure.

    gasto acum = 
IF(MAX('Table'[periodo])<=CONVERT(FORMAT(TODAY(),"yyyymm"),INTEGER),
CALCULATE(SUM('Table'[gasto]),FILTER(ALLSELECTED('Table'),[periodo]<=MAX('Table'[periodo]))))

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      3 years ago

      Hi @Syndicate_Admin, thanks for the answer, it is a partial help to the problem I have, but as we see a straight line is still drawn for the current month, 202211.
      At the moment I have overcome it in this way: 

      gasto acum = 
VAR fecha_resta_mes = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) )
VAR Result = 
IF(
  MAX('Table'[periodo])<=CONVERT(FORMAT(fecha_resta_mes,"yyyymm"),INTEGER),
  CALCULATE(
    SUM('Table'[gasto]),
    FILTER(
      ALLSELECTED('Table'),[periodo]<=MAX('Table'[periodo])
    )
  )
)
RETURN
    Result

      Obtained this result:


      I would like to know if anyone has any better solution, since for the moment I would be leaving it at that.
      Thank you.