Forum Discussion

gbarr12345's avatar
gbarr12345
Post Prodigy
2 years ago
Solved

Cumulative Total query

Hi,

 

I have data that I've been asked to include a cumulative total beside.

 

All the data is from the 1st day of various months:

 

 

 

However when I add the cumulative total, it puts it inside dates for the middle of the month which leaves blanks in the volume section:

 

 

 

How do I get the blanks to not appear?

 

My current measure code is below:

 

Depletion Cumulative Total =
CALCULATE(
    SUM(Depletion[9LE]),
    FILTER(
        ALL(Dim_Date[Date]),
        Dim_Date[Date] <= MAX(USA[Date])
    )
)
Need Help
  • ahadkarimi's avatar
    ahadkarimi
    2 years ago

    Try this, and let me know if there are any problems:

    Depletion Cumulative Total =
    IF ( NOT ISBLANK(SUM(Depletion[9LE])),
    CALCULATE(
    SUM(Depletion[9LE]),
    FILTER(
    ALLSELECTED(Dim_Date[Date]),
    Dim_Date[Date] <= MAX(Dim_Date[Date])
    )))

5 Replies

  • ahadkarimi's avatar
    ahadkarimi
    Solution Specialist
    2 years ago

    Hi, you can try this to remove blanks:
    Depletion Cumulative Total =
    IF ( ISBLANK(SUM(Depletion[9LE])),
    BLANK(),
    CALCULATE( SUM(Depletion[9LE]),

    FILTER( ALL(Dim_Date[Date]), Dim_Date[Date] <= MAX(USA[Date]))))

    • gbarr12345's avatar
      gbarr12345
      Post Prodigy
      2 years ago

      Hi,

       

      Thank you for your response. 

       

      I tried this but it doesn't seem to work:

       

       

      • ahadkarimi's avatar
        ahadkarimi
        Solution Specialist
        2 years ago

        Try this, and let me know if there are any problems:

        Depletion Cumulative Total =
        IF ( NOT ISBLANK(SUM(Depletion[9LE])),
        CALCULATE(
        SUM(Depletion[9LE]),
        FILTER(
        ALLSELECTED(Dim_Date[Date]),
        Dim_Date[Date] <= MAX(Dim_Date[Date])
        )))