Past 3 and 6 months data base on different date for each category

[yybi123]

Hi, 

My main dataset is a Sales data with invoice date for different categories. What I want to achieve is to calculate the sum of sales for past 3 and 6 mths for each category base on the start date. I have a mapping table of the category with the start date.
For example, 

Category	Start Date
A	01/01/2022
B	01/02/2022
C	15/01/2022

I tried using CALCULATE([Total Sales],DATESINPERIOD('Date'[Date], MAX('Category'[Start Date]),-6,MONTH)) this will give me the correct value when I filter to each category but when there is no filter applied it will not calculate the past 6M sales base on each category start date. I think the issue when there is no filter it will take the Max start date which is 01/02/2022 to calculate for all 3 categories. How can I modify the DAX for it to calculate each category sales base on its start date?

[v-yangliu-msft · Accepted solution]

Hi  @yybi123 ,

Here are the steps you can follow：

1. Create measure.

Past 3M sales =
var _select=SELECTEDVALUE('Table1'[Category])
var _selectdate=MAXX(FILTER(ALL('Table1'),'Table1'[Category]=_select),[Start Date])
var _3m=CALCULATE(SUM('Table2'[Sales]),FILTER(ALL(Table2),'Table2'[Category]=_select&&
'Table2'[Invoice Date]>=DATE(YEAR(_selectdate),MONTH(_selectdate)-3,DAY(_selectdate))&&
'Table2'[Invoice Date]<_selectdate))
var _min=MINX(ALL('Table1'),'Table1'[Start Date])
var _total=
CALCULATE(SUM('Table2'[Sales]),FILTER(ALL(Table2),
'Table2'[Invoice Date]>=DATE(YEAR(_min),MONTH(_min)-3,DAY(_min))&&
'Table2'[Invoice Date]<_min))
return
IF(
     HASONEVALUE('Table1'[Category]),_3m,_total)

Past 6M sales =
var _select=SELECTEDVALUE('Table1'[Category])
var _selectdate=MAXX(FILTER(ALL('Table1'),'Table1'[Category]=_select),[Start Date])
var _6M=
CALCULATE(SUM('Table2'[Sales]),FILTER(ALL(Table2),'Table2'[Category]=_select&&
'Table2'[Invoice Date]>=DATE(YEAR(_selectdate),MONTH(_selectdate)-6,DAY(_selectdate))&&
'Table2'[Invoice Date]<_selectdate))
var _min=MINX(ALL('Table1'),'Table1'[Start Date])
var _total=
CALCULATE(SUM('Table2'[Sales]),FILTER(ALL(Table2),
'Table2'[Invoice Date]>=DATE(YEAR(_min),MONTH(_min)-6,DAY(_min))&&
'Table2'[Invoice Date]<_min))
return
IF(
     HASONEVALUE('Table1'[Category]),_6M,_total)

2. Result:

Slicer selection is B:

Slicer not selected:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[yybi123]

I will try to illustrate with more details.

My sales table will be with Invoice Date, Category and Sales Amount. 

Invoice Date	Category	Sales
01/10/2021	A	0.5
01/10/2021	B	1
01/10/2021	C	2
16/10/2021	C	3
01/11/2021	A	0.5
01/11/2021	B	1
01/11/2021	C	2
01/12/2021	A	1
01/12/2021	B	1
01/12/2021	C	1
01/01/2022	A	3
01/01/2022	B	2
01/01/2022	C	1

My desired results of Past 3M sales will be like this. The past 3M sales will be calculated for each category base on their own start date . For example , base on Category C start date i would only sum up sales from 15/10/21 to 14/01/22.  

Category	Start Date	Past 3M sales
A	01/01/2022	2
B	01/02/2022	4
C	15/01/2022	6

When there is no filter applied the Past 3M sales should be 12 which is  (2+4+6).

But with my measure I created  - CALCULATE([Total Sales],DATESINPERIOD('Date'[Date], MAX('Category'[Start Date]),-6,MONTH)) it will give me Sales from 1/10/2021 to 31/01/2022 which is 12.5 and not correct as it took the max of start date of the 3 to calculate instead of considering each category different start date. 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[yybi123]

Thanks. I tried using the measire but the calculation is not correct for B and C.
For example,  B start date is 1st Feb , it should sum up sales from Jan, Dec 21 and Nov 21 but the measure gave me 3. 

I think the issue is I can see you have used Min(Start Date) which mean all the Last 3M will be calculated base on the  min of start date but not the start date of each category itself.

[Ashish_Mathur]

You are welcome.  When i select Category B, I see the result as 4.

[bvanderwatt]

Hi Ashish

Hope you're very well. 

I tried your above measure but I couldnt get it to work. 😞 

I am looking to add a measure to determine the sales with the first 6 months and first 12 months from the first date I sold a product. 

I have added a calculated column to determine the first transaction date and the transaction date after 6 months. 

I've tried the below measure but still no luck. 😞 

Thought you could save the day 🙂 

Sales 3 months ended the start date =

CALCULATE(

'Combined Sales'[Sum of Inv+Order],

DATESBETWEEN(

'Calendar'[Date],

edate('Combined Sales'[FirstTransactionDate]-1,-3),

'Combined Sales'[FirstTransactionDate]-1))

[Ashish_Mathur]

Hi,

Do not create a calculated column formula.  Write these measures

Sales = sum('Combined Sales'[Sum of Inv+Order])

Date of first transaction = calculate(min('Combined Sales'[Date]),datesbetween('Calendar'[Date],MINX(ALL('Calendar'),'Calendar'[Date]),max('Calendar'[Date])))

Sales within 6 months of first transaction = calculate([sales],datesbetween('Calendar'[Date],[Date of first transaction],edate([Date of first transaction],6)))

Hope this helps.

[bvanderwatt]

Thank you so much @Ashish_Mathur 

I really appreciate your help. 

I've picked up that sometime the first value is "0", how would I be able to amend your filter to only include where the units invoiced is > 0? Again, I appreciate your help more than you can imagine 🙂 

First Transaction Date =

CALCULATE(

    MIN(

        vw_fd_SalesCombined[InvoiceDate]),

        DATESBETWEEN(

            'Calendar'[Date],

            MINX(

                ALL(

                    'Calendar'),

                    'Calendar'[Date]),

                    MAX(

                        'Calendar'[Date]

                    )

        )

)

[Ashish_Mathur]

Hi,

Try this measure

Sales within 6 months of first transaction = calculate([sales],datesbetween('Calendar'[Date],[Date of first transaction],edate([Date of first transaction],6)),filter(values(Data[CustomerID]),[sales]>0))

[Ashish_Mathur]

Hi,

Show a larger dataset and also clearly show the expected result when no filter is applied.