Calculate accumulated Customer Order and next month Customer Order in same matrix

[BryanJun]

I'm trying to create a matrix/table visual that contain the total customer order until a certain month (ex. Dec 2022) and the next month (Jan 2023) customer order.

Dataset:

Item	Date	Customer Order (QTY)
Ice Cream	01/01/2021	10
Ice Cream	02/02/2021	10
Candy	01/02/2021	20
Ice Cream	01/12/2022	10
Candy	02/12/2022	20
Ice Cream	03/12/2022	20
Ice Cream	04/12/2022	30
Ice Cream	01/01/2023	30
Candy	02/01/2023	20
Ice Cream	03/01/2023	20
Ice Cream	02/02/2023	10
Ice Cream	03/02/2023	20
Candy	04/02/2023	30
Ice Cream	01/03/2023	10
Ice Cream	02/03/2023	20
Candy	03/03/2023	30

for example i want to check the accumulated month Customer Order in Dec 2022, it should add all the previous date Customer Order Qty until Dec 2022 (red color), and the next month Customer Order QTY will be add for Jan 2023 only (blue color).

The result should be like:

	Until Dec-2022	Jan-23
Ice Cream	80	50
Candy	40	20

Is this possible to done in a single matrix/table visual?

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[BryanJun]

Hi,

since it shows all the future QTY in every month, can it be limit by just showing next 1 month (Jan-2023)?

[Ashish_Mathur]

Try to edit the measure yourself.  Try also to change the filter condition in the filter pane.

[amitchandak · Accepted solution]

@BryanJun , You can have two measures

This Month Today =
var _min = eomonth(today(),-1)+1
var _max = eomonth(today(),0)
return CALCULATE(Sum(Table[Customer Qty]), FILTER('Table','Table'[Date] >=_min && 'Table'[Date] <= _max))

before this month

 =
var _max = eomonth(today(),-1)
return CALCULATE(Sum(Table[Customer Qty]), FILTER('Table', 'Table'[Date] <= _max))

based on selected date or today

This Month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
var _min = eomonth(_max,-1)+1
return
return CALCULATE(Sum(Table[Customer Qty]), FILTER('Table','Table'[Date] >=_min && 'Table'[Date] <= _max))

before This Month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),-1)
return
return CALCULATE(Sum(Table[Customer Qty]), FILTER('Table', 'Table'[Date] <= _max))

[BryanJun]

Hi,

Thank you, this is what i want!!

Is that having any method to change the column name dynamically according to current month and year?

(ex: Dec-2022, Jan-2023)