Skip to main content
cancel
Showing results for 
Search instead 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

Reply
BryanJun
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:

ItemDateCustomer Order (QTY)
Ice Cream        01/01/2021       10
Ice Cream  02/02/2021

10

Candy   01/02/202120
Ice Cream   01/12/202210
Candy02/12/202220
Ice Cream03/12/202220
Ice Cream04/12/202230
Ice Cream01/01/202330
Candy02/01/202320
Ice Cream03/01/202320
Ice Cream02/02/202310
Ice Cream03/02/202320
Candy04/02/202330
Ice Cream01/03/202310
Ice Cream02/03/202320
Candy03/03/202330

 

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       8050
Candy4020

 

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

1 ACCEPTED SOLUTION
amitchandak
Super User
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))

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
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))

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)

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.