Cumulative Sales month wise

[Anonymous]

Hi Guys

here i am facing issue related to Cumulative total values.

this is my cumulative Monthly sales measure 

Cumulative Query for monthly

Here, I created Normal matrix and Cumulative matrix.

Normal Matrix & Cumulative Matrix

2nd matrix showing sales values are wrong, i mean cumulative matrix may - 20 it's showing zero(0.0M) bcz of orginal matrix have 0 values in may 20.

But i need value in may 20 ->(2366.9M+0.0M) = 2366.9M Cumulative matrix.

Cumulative total will showing perfect fine may-20 (2366.9+379.6+4450.1+4545.0+0.0 = 11,741.5), but when coming to month wise it's not showing.

regards,

naveen

[v-yingjl · Accepted solution]

Hi @Anonymous ,

You can create a measure like this:

Measure =
SUMX (
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'DIM_TIME' ),
            'DIM_TIME'[Date] <= MIN ( 'DIM_TIME'[Date] )
                && 'DIM_TIME'[Product] IN DISTINCT ( 'DIM_TIME'[Product] )
        ),
        'DIM_TIME'[Month_Year],
        "MeasureValue", CALCULATE ( [Actual Net flow] )
    ),
    [MeasureValue]
)

The result may be your expected:

Here is my sample file that hopes to help you too, please check and try it: Cumulative Sales month wise.pbix 

Best Regards,
Yingjie Li

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

[amitchandak]

@Anonymous , I ncase you have a date in your table, you can use datesmtd with a date table

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/