Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
yybi123
Helper I
Helper I

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

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, 

CategoryStart Date
A01/01/2022
B

01/02/2022

C15/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?

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_2-1648603847431.png

Slicer not selected:

vyangliumsft_3-1648603867099.png

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

View solution in original post

10 REPLIES 10
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_2-1648603847431.png

Slicer not selected:

vyangliumsft_3-1648603867099.png

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
Helper I
Helper I

I will try to illustrate with more details.

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

Invoice DateCategorySales
01/10/2021A0.5
01/10/2021B1
01/10/2021C2
16/10/2021C3
01/11/2021A0.5
01/11/2021B1
01/11/2021C2
01/12/2021A1
01/12/2021B1
01/12/2021C1
01/01/2022A3
01/01/2022B2
01/01/2022C1

 

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.  

 

CategoryStart DatePast 3M sales
A01/01/2022 2
B01/02/2022 4
C15/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. 

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/

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. 

yybi123_0-1648040660648.png

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.

 

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

Untitled.png


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

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

bvanderwatt_2-1655242613248.png

 

 

bvanderwatt_0-1655242274211.pngbvanderwatt_1-1655242294563.png

 

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.


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

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

 

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


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

Hi,

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


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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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