Commulative with Month Slicer

[Jyaul1122]

Dear Helper,

i have two tables Calendar and Sales, i need to get sales amount Commulative and want display on Column chart.

Calendar table:

and Sales table:

Measures i wrote:  

Measure Sales =

var min_date=CALCULATE(MIN('Calendar'[Date]),ALL('Calendar'))

return

CALCULATE(SUM('Table'[Sales]),DATESBETWEEN('Calendar'[Date],min_date,MAX('Calendar'[Date])))

i have Month slicer and Column chart on my report.

Whenever i select slicer month May-2024, i want to get Column from May-2024 to Jan 2024.  

Whenever i select slicer month Apr-2024, i want to get Column from Apr-2024 to Jan 2024.  

Whenever i select slicer month March-2024, i want to get Column from March-2024 to Jan 2024.  

Whenever i select slicer month Feb-2024, i want to get Column from Feb-2024 to Jan 2024.  and so.

How can i achive this.

[v-rongtiep-msft]

Hi @lbendlin ,

Thanks for your quick response.

Hi @Jyaul1122 ,

Please refer to my pbix file to see if it helps you.

Create a measure.

MeasureSales =
VAR _1 =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] <= SELECTEDVALUE ( 'Calendar'[Date] )
                && 'Table'[Date] <= SELECTEDVALUE ( 'Table'[Date] )
        )
    )
VAR _2 =
    SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    IF ( _2 >= MAX ( 'Table'[Date] ), _1, BLANK () )

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jyaul1122]

@v-rongtiep-msft 

Thanks for your effort and help. You are using Table[Date],Table[Sales],MeasureSales in table visual.

Actually in my PBIX those type of visual using data from multiple table related with Calendar table. So that i would like have visual with data: Calendar[Date],Table[Sales],MeasureSales. 

Hope understand my requirements.

Thanks.

[lbendlin]

Have you considered using the Quick Measures?  There the pattern is called "Running Total"

[Jyaul1122]

I tried with Quick Measure but it does not work. Please let me know if you have another idea.

Thanks

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Jyaul1122]

@lbendlin 

For your information, sales table:Table

Date	Sales
01-Jan-2024	10
01-Feb-2024	15
01-Mar-2024	20
01-Apr-2024	25
01-May-2024	30

and calendar table: CALENDARAUTO(12)

the measure , I am using: 

Measure Sales =

var min_date=CALCULATE(MIN('Calendar'[Date]),ALL('Calendar'))

return

CALCULATE(SUM('Table'[Sales]),DATESBETWEEN('Calendar'[Date],min_date,MAX('Calendar'[Date])))