cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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?

1 ACCEPTED SOLUTION
Super User

@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))

5 REPLIES 5
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Hi,

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

Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

@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))

Frequent Visitor

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)