Cumulative total with a slicer did not give the cumulative total monthwise

[kskumar]

Dear Folks,

I have two tables and relationship as shown below.

1. Financials

2. DateTable

I have created a slicer with the Date table and also created a measure in Financial table for cumulative sales as shown below.

Cumulative Sales = CALCULATE(SUM(financials[ Sales]),FILTER(ALL(DateTable),DateTable[Date] <= MAX(DateTable[Date])))

When I choose the date in slicer as "Feb2014" month (for example), am NOT getting the cumulative sales (rather am getting the respective sales for each month) . What I actually need is the cumulative sales starting from the beginning (i.e if I bring the date field from Financial table as one of the column, I need the cumulative sales alongside each date until the date I choose from Slicer).

Can someone PLEASE help me how to achieve the cumulative sales for each month (until the month I choose from Slicer)?

Kindly let me know if you need any information from my side.

I can provide my PBIX file if required.

Thank you.

Current Output:

------------------

Regards

Kumar

[Ashish_Mathur]

Hi,

Try this measure:

Cumulative Sales = CALCULATE(SUM(financials[ Sales]),DATESBETWEEN(DateTable[Date],MINX(ALL(dateTable),DateTable[Date]),MAX(DateTable[Date])))

[kskumar]

Hi @Ashish_Mathur ,

Thanks for your response. Have tried you using the measure as you suggested. It gives me the sum for the respective year as shown below (please refer to the measure - Cumulative Sales 4).  Kindly note that I have applied xifeng solution and got the result (pls refer to the measure - Cumulative Sales 3).

[Ashish_Mathur]

I canno do much with a screenshot.  Show the expected result very clearly.  Share the download link of the PBI file.

[xifeng_L]

Hi @kskumar ,

You can try below measure:

Cumulative Sales = 
VAR SlicerDate = MAX(DateTable[Date])
VAR RowDate = CALCULATE(MAX(Financials[Date]),ALL(DateTable))
RETURN
CALCULATE(
    IF(NOT ISEMPTY(financials),
        CALCULATE(
            SUM(financials[ Sales]),
            FILTER(ALL(Financials[Date]),Financials[Date] <= RowDate )
        )
    ),
    FILTER(ALL(DateTable),DateTable[Date] <= SlicerDate )
)

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[kskumar]

Thank you very much @xifeng_L for your quick response. 

This solution works perfectly showing the Cumulative sales. However, If I bring the year into the column list, it did not show as expected. I also have a column "Fiscal Year" in table "Financials", as I need to show the "sales" financial year wise (in my case, financial year is from Jul to Jun).  so If I bring "Fiscal year" or "Year" into the column list, it shows the values only for the respective year (not as running sales total).  If you see the below screenshot, I see the sales for respective Fiscal year (instead of running total).

Can you please advise me how to achieve the running total - fiscal year wise. 

Thanks again for your help. Please let me know if you need my PBIX file/ any other information you may require.

Current output:

Sample Data from "financial" table

[xifeng_L · Accepted solution]

Let's slightly modify the expression of the measure, pls try again.

Cumulative Sales = 
VAR SlicerDate = MAX(DateTable[Date])
VAR RowDate = CALCULATE(MAX(Financials[Date]),ALL(DateTable))
RETURN
CALCULATE(
    IF(NOT ISEMPTY(financials),
        CALCULATE(
            SUM(financials[ Sales]),
            FILTER(ALLEXCEPT(Financials,DateTable),Financials[Date] <= RowDate )
        )
    ),
    FILTER(ALL(DateTable),DateTable[Date] <= SlicerDate )
)

[kskumar]

Thank you @xifeng_L . 

The suggested solution works perfectly. Have accepted the given solution.