ONE Sales Data Table, TWO sumSALES Columns, TWO Date Slicers/Filters for each sumSales Column

[Anonymous]

Hello Community,

I am wondering how to apply the following logic in POWER BI:

I have a table with the sales fact data such as date, product, quantity, price, line amount and so on and a Calendar table which has a relationship with the sales table.

Therefore, I have a matrix/table, showing sold quantity per product and a date slicer.

Product name	Sold Quantity
Product A	10
Product Б	20
Product C	30
Product D	40

So far so good!

I want to add a second column for sold quantity for a different period. The matrix should look like this:

Product name	Sold Quantity Period 1	Sold Quantity Period 2
Product A	10	5
Product Б	20	15
Product C	30	25
Product D	40	35

There should be two date slicers:

- DATE SLICER PERIOD 1, filtering only column Sold Quantity Period 1

- DATE SLICER PERIOD 2, filtering only column Sold Quantity Period 2

I have tried parameters, variables and other techniques, described in PBI community knowledge base/forum, but with no results.

Thank you advance for your time.

Kind regards!

[v-danhe-msft]

Hi @Anonymous ,

What is your date table looks like? Could you please offer your sample data and post your desired result if possible?

Regards,

Daniel He

[Anonymous]

Hi @v-danhe-msft ,

the date table is a generic calendar table. Here is an example:

Date

MonthNum

Month

MonthLong

Quarter

Year

FiscalMonthNum

FiscalMonth

FiscalMonthLong

FiscalQuarter

FiscalYear

MonthYearNum

MonthYear

Weekday

WeekdayWeekend

WeekSequenceNum

1.7.2015

7

Jul

July

Q3

2015

1

Jul

July

FQ1

FY16

201507

Jul-15

Wed

Weekday

27

2.7.2015

7

Jul

July

Q3

2015

1

Jul

July

FQ1

FY16

201507

Jul-15

Thu

Weekday

27

5.7.2015

7

Jul

July

Q3

2015

1

Jul

July

FQ1

FY16

201507

Jul-15

Sun

Weekday

28

Sales Data Table looks like:

Product ID	Quantity	Store ID	Document No	Document Date
1	5	1	1000000001	1.7.2015
2	10	1	1000000001	2.7.2015
3	15	1	1000000001	3.7.2015
4	20	1	1000000001	4.7.2015

Relationship is between both tables done through columns Calendar.Date & SalesData.DocumentDate

As a result I expect the following:

Filters
Period 1:	1.01.2015 - 02.07.2015
Period 2:	3.01.2015 - 04.07.2015
Product ID	SOLD Quantity Period 1	Sold Quantity Period 2
1	5	0
2	10	0
3	0	15
4	0	20

Actually, the abovementioned periods are used for illustrating the desired effect but they would not be so short in reality.

Kind regards,

[v-danhe-msft · Accepted solution]

Hi @Anonymous ,

Based on my test, you could refer to below formula:

Measure = CALCULATE(MAX('Table2'[Sold Quantity]),FILTER('Table1','Table1'[Document Date] >=MINX('Calender',Calender[Date])&&'Table1'[Document Date] <=MAXX('Calender',Calender[Date])))

Measure2 = CALCULATE(MAX('Table2'[Sold Quantity]),FILTER('Table1','Table1'[Document Date] >=MINX('Calender2',Calender2[Date])&&'Table1'[Document Date] <=MAXX('Calender2',Calender2[Date])))

Pay attention that I have copied a calender table to show two time period:

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He