DAX for plotting Recurrence of recently ordered customers on Stacked bar chart

[Anonymous]

Hi Guys,

I have just started my journey with PowerBi. Stuck with a scenario where i have to plot customers for selected month on stacked bar chart and their earlier order months(months will be on or before selected month) on legend and sorting the customers based on number of order months in descending order. 

Customer	OrderMonth	IsOrderPlaced
Customer1	Jan-19	1
Customer1	Feb-19	1
Customer1	Mar-19	1
Customer2	Jan-19	1
Customer2	Mar-19	1
Customer3	Mar-19	1
Customer3	Feb-19	1
Customer3	Dec-19	1
Customer4	Mar-19	1
Customer4	Feb-19	1

1.If User selects Feb-19, charts shall display custmers who placed order in Feb-19 and their earlier order months as legend.

2.Sorting of customers shall be based on order months count till selected date

I tried below DAX expression with saperate table for slicer month. 
CALCULATE(SUM(Order[IsOrderPlaced]),FILTER(ALLSELECTED(Order[OrderMonth]),Order[OrderMonth]=MAx(Slicer[SlicerAnalysisMonth])))

The above dax expression is displying selected month customers but the legend is diplaying future values too. Sorting order is also not woring.

Any help would be  highly appricited.

Thanks in Advance.

Regards,

Hari

[amitchandak]

Are you looking for cumulative, In case you have the date you can get that easily with the help of the date table,

Or try to convert month into a date

Date = "1-"& Table[Month-year] //in format MMM-YYYY .And change the datatype to date.

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[Anonymous]

@amitchandak , Thanks for your reply. It is not working for me.

I am tryining to display selected month customer's historical orders by month.

For exapmle if user slects Dec-2019, DAX expression shoud return Costomers who ordered in Dec-2019 and their previous order months. My dimensions are Customer and OrderMonth.

I tried ALL() and AllSelected() but no luck.

Thanks,

Hari

[amitchandak]

Convert your month to date that would be better. Even to make the best use of all, you have to create an order month dimension. Then you all will work better.

Date = "01"-Table[Month-Year] //looking at the current format and then mark it as the date. Or try marking the current month as the date it will convert to date