Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
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
learner03
Post Partisan
Post Partisan

Current, Past 3 and past 6 months

‎03-06-2022 06:47 PM

I have 2 tables-

Order status

Day differnce tell day between ETA abd Actual Delivery date. Difference column shows 1 whwere it was on time and 0 when delayed.

Created FromDateETAActual Delivery Date Day DifferenceDifferenceBudget Category
SO00310359Thursday, 3 March 20227/03/2022Monday, 7 March 202201Retail 
SO00311030Thursday, 3 March 20227/03/2022Friday, 4 March 202231Promotional
SO00310646Tuesday, 1 March 202224/02/2022Wednesday, 23 February 202211Hospitality
SO00310853Tuesday, 1 March 20223/03/2022Wednesday, 2 March 202211Promotional
SO00310929Tuesday, 1 March 20223/03/2022Thursday, 3 March 202201Hospitality
SO00310960Tuesday, 1 March 20224/03/2022Thursday, 3 March 202211Promotional
SO00310479Tuesday, 1 March 20224/03/2022Thursday, 3 March 202211Promotional
SO00310849Tuesday, 1 March 20224/03/2022Thursday, 3 March 202211Promotional
SO00310885Monday, 28 February 20222/03/2022Wednesday, 2 March 202201Hospitality
SO00310629Friday, 18 February 202222/02/2022Tuesday, 22 February 202201Promotional
SO00310501Wednesday, 16 February 202218/02/2022Thursday, 17 February 202211Promotional
SO00310131Wednesday, 16 February 202222/02/2022Friday, 18 February 202241Hospitality
SO00310422Wednesday, 16 February 202218/02/2022Thursday, 17 February 202211Hospitality
SO00310538Wednesday, 16 February 202218/02/2022Thursday, 17 February 202211Promotional
SO00310488Wednesday, 16 February 202218/02/2022Thursday, 17 February 202211Hospitality
SO00310516Wednesday, 16 February 202218/02/2022Wednesday, 2 March 2022-120Promotional
SO00310267Wednesday, 9 February 202211/02/2022  1Promotional
SO00310050Tuesday, 8 February 202216/02/2022Wednesday, 16 February 202201Hospitality
SO00309741Tuesday, 8 February 202210/02/2022  1Promotional
SO00310263Tuesday, 8 February 202210/02/2022  1Promotional
SO00310124Thursday, 3 February 20227/02/2022  1Promotional
SO00310133Thursday, 3 February 20227/02/2022  1Hospitality
SO00309243Friday, 14 January 202218/01/2022Monday, 17 January 202211Hospitality
SO00308728Friday, 14 January 202220/01/2022Tuesday, 18 January 202221Hospitality
SO00308565Friday, 14 January 202231/01/2022Monday, 24 January 202271Hospitality
SO00308478Friday, 14 January 202231/01/2022Friday, 21 January 2022101Hospitality
SO00308830Wednesday, 12 January 202217/01/2022Friday, 14 January 202231Promotional
SO00309127Wednesday, 12 January 202214/01/2022Friday, 14 January 202201Promotional
SO00309120Wednesday, 12 January 202214/01/2022Friday, 14 January 202201Promotional
SO00309094Wednesday, 12 January 202217/01/2022Thursday, 13 January 202241Promotional
SO00309481Wednesday, 12 January 202217/01/2022Thursday, 13 January 202241Hospitality
SO00309261Tuesday, 11 January 202213/01/2022Friday, 14 January 2022-10Hospitality
SO00309129Tuesday, 11 January 202214/01/2022Wednesday, 19 January 2022-50Hospitality
SO00309001Wednesday, 5 January 20227/01/2022Monday, 10 January 2022-30Hospitality
SO00309041Thursday, 30 December 20214/01/2022Wednesday, 5 January 2022-10Hospitality
SO00309017Thursday, 30 December 20215/01/2022Tuesday, 4 January 202211Hospitality
SO00309104Wednesday, 29 December 202131/12/2021Thursday, 30 December 202111Hospitality
SO00308511Wednesday, 29 December 20214/01/2022Friday, 31 December 202141Hospitality
SO00308512Wednesday, 29 December 20214/01/2022Friday, 31 December 202141Hospitality
SO00308825Thursday, 23 December 202129/12/2021Friday, 24 December 202151Hospitality
SO00308867Wednesday, 22 December 202124/12/2021Thursday, 23 December 202111Hospitality
SO00308517Wednesday, 22 December 202111/01/2022Wednesday, 5 January 202261Hospitality
SO00308842Wednesday, 22 December 202124/12/2021Thursday, 23 December 202111Hospitality
SO00307116Tuesday, 21 December 202123/12/2021Thursday, 23 December 202101Hospitality
SO00309000Tuesday, 21 December 202124/12/2021Friday, 24 December 202101Promotional
SO00308783Tuesday, 21 December 202123/12/2021Wednesday, 22 December 202111Hospitality
SO00307973Tuesday, 21 December 202130/12/2021Thursday, 30 December 202101Hospitality
SO00309044Tuesday, 21 December 20214/01/2022Tuesday, 4 January 202201Hospitality
SO00308504Tuesday, 21 December 202123/12/2021Wednesday, 22 December 202111Promotional
SO00308824Monday, 20 December 202122/12/2021Tuesday, 21 December 202111Hospitality
SO00308465Monday, 20 December 202122/12/2021Wednesday, 29 December 2021-70Promotional
SO00308823Monday, 20 December 202123/12/2021Wednesday, 22 December 202111Hospitality
SO00308863Monday, 20 December 202122/12/2021Tuesday, 21 December 202111Hospitality
SO00308697Monday, 20 December 202122/12/2021Tuesday, 21 December 202111Hospitality
SO00308991Monday, 20 December 202122/12/2021Tuesday, 21 December 202111Promotional
SO00308787Monday, 20 December 202122/12/2021Tuesday, 21 December 202111Hospitality
SO00308992Monday, 20 December 202123/12/2021Thursday, 23 December 202101Hospitality
SO00307774Monday, 20 December 202122/12/2021Tuesday, 21 December 202111Hospitality
SO00308924Monday, 20 December 202122/12/2021Wednesday, 22 December 202101Promotional
SO00308732Monday, 20 December 202122/12/2021Wednesday, 22 December 202101Hospitality
SO00308736Monday, 20 December 202122/12/2021Wednesday, 22 December 202101Hospitality
SO00308444Friday, 17 December 202121/12/2021Monday, 20 December 202111Promotional
SO00308740Friday, 17 December 202121/12/2021Monday, 20 December 202111Retail 
SO00308674Friday, 17 December 202122/12/2021Tuesday, 21 December 202111Hospitality
SO00308389Friday, 17 December 202130/12/2021Friday, 24 December 202161Promotional
SO00308510Friday, 17 December 202130/12/2021Wednesday, 22 December 202181Hospitality
SO00308913Friday, 17 December 202122/12/2021Tuesday, 11 January 2022-200Promotional
SO00308888Friday, 17 December 202121/12/2021Tuesday, 21 December 202101Hospitality
SO00308549Friday, 10 December 202114/12/2021Monday, 13 December 202111Promotional
SO00308464Friday, 10 December 202114/12/2021Tuesday, 14 December 202101Promotional
SO00308466Friday, 10 December 202117/12/2021Friday, 17 December 202101Promotional
SO00308367Thursday, 9 December 202120/12/2021Wednesday, 15 December 202151Hospitality
SO00308439Thursday, 9 December 202114/12/2021Tuesday, 21 December 2021-70Hospitality
SO00308467Thursday, 9 December 202115/12/2021Tuesday, 14 December 202111Hospitality
SO00308373Thursday, 9 December 202113/12/2021Friday, 10 December 202131Promotional
SO00308420Thursday, 9 December 202117/12/2021Wednesday, 15 December 202121Promotional
SO00307158Thursday, 9 December 202116/12/2021Wednesday, 15 December 202111Hospitality
SO00308384Thursday, 9 December 202114/12/2021Wednesday, 15 December 2021-10Hospitality
SO00308379Thursday, 9 December 202114/12/2021Monday, 13 December 202111Hospitality
SO00308397Thursday, 9 December 202121/12/2021Monday, 20 December 202111Hospitality
SO00308368Thursday, 9 December 202120/12/2021Wednesday, 15 December 202151Hospitality
SO00308413Thursday, 9 December 202113/12/2021Friday, 10 December 202131Hospitality
SO00308296Wednesday, 8 December 202114/12/2021Monday, 13 December 202111Hospitality
SO00308323Wednesday, 8 December 202113/12/2021Monday, 13 December 202101Retail 
SO00308058Wednesday, 8 December 202110/12/2021Friday, 10 December 202101Promotional
SO00308161Wednesday, 8 December 202110/12/2021Thursday, 9 December 202111Promotional
SO00308298Wednesday, 8 December 202110/12/2021Thursday, 9 December 202111Promotional
SO00308234Wednesday, 8 December 202114/12/2021Monday, 13 December 202111Promotional
SO00308329Wednesday, 8 December 202110/12/2021Saturday, 11 December 2021-10Hospitality
SO00307911Wednesday, 8 December 202110/12/2021Thursday, 9 December 202111Hospitality
SO00308322Wednesday, 8 December 202113/12/2021Monday, 13 December 202101Retail 
SO00308326Wednesday, 8 December 202113/12/2021Friday, 10 December 202131Hospitality
SO00307636Wednesday, 8 December 202121/12/2021Wednesday, 29 December 2021-80Hospitality
SO00308110Tuesday, 7 December 20219/12/2021Wednesday, 8 December 202111Promotional
SO00308370Tuesday, 7 December 20219/12/2021Wednesday, 8 December 202111Promotional
SO00307191Tuesday, 7 December 20219/12/2021Monday, 13 December 2021-40Hospitality
SO00307960Tuesday, 7 December 202110/12/2021Friday, 10 December 202101Promotional
SO00308231Tuesday, 7 December 20219/12/2021Wednesday, 8 December 202111Promotional

second table is calender-

akapoor03_0-1646620780830.png

I need something like below-

Budget categorytotal of difference Column where there is 1 Current monthtotal of difference Column where there is 1 Past 3 monthstotal of difference Column where there is 1 Past 6 months
    
    
    
    

 

Labels:
Message 1 of 6
462 Views
0
2 ACCEPTED SOLUTIONS
AllisonKennedy
Super User
Super User
In response to learner03

‎03-07-2022 01:23 PM

@learner03 You can change the PeriodStart variable:

 

VAR _PeriodStart = EOMONTH(_MaxDate, -1)


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

Message 4 of 6
366 Views
AllisonKennedy
Super User
Super User
In response to learner03

‎03-08-2022 01:05 AM

@learner03 You can just add +1 to make it the 1 March, but keep in mind the _PeriodStart variable is giving the end of the month to start the DATESINPERIOD() function, and then count backwards the number of months you put in the _Length variable, so not sure why you'd want it to be 1 March? 28 Feb seems like the correct start date to give past 1 month not counting this month??


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

Message 6 of 6
332 Views
5 REPLIES 5
AllisonKennedy
Super User
Super User

‎03-06-2022 07:18 PM

@learner03 You should be able to do this with DATESINPERIOD:

https://dax.guide/datesinperiod/

 

Count Orders Delayed This Month =
VAR _MaxDate = TODAY() -- can make this MAX(Calendar[Date]) instead
VAR _Length = 1 -- 1 for current month, 2 for past month and current month, 3 for past 2 months and current month, etc
VAR _PeriodStart = EOMONTH(_MaxDate, 0)
VAR _Orders = CALCULATE ( SUM('Order Status'[Difference]) , DATESINPERIOD ( Calendar[Date], _PeriodStart, -_Length, MONTH))
RETURN
_Orders
 
See attached file below signature.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Current Past 3 months.pbix
Message 2 of 6
420 Views
learner03
Post Partisan
Post Partisan
In response to AllisonKennedy

‎03-06-2022 10:20 PM

@AllisonKennedy This works. Thanks. What if I want to exclude Current month from ast 3 or past 6.?

Message 3 of 6
390 Views
0
AllisonKennedy
Super User
Super User
In response to learner03

‎03-07-2022 01:23 PM

@learner03 You can change the PeriodStart variable:

 

VAR _PeriodStart = EOMONTH(_MaxDate, -1)


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Message 4 of 6
367 Views
learner03
Post Partisan
Post Partisan
In response to AllisonKennedy

‎03-07-2022 08:23 PM

@AllisonKennedy I tried at my data and somewhere struggling. When I see _period start, it is giving 28 Feb instead of 1st march.

 

Attached is the data file. Can you please help https://drive.google.com/file/d/1jj-af_hwm78-1JeNDutZw5mUiFpKYVD1/view?usp=sharing 

Message 5 of 6
344 Views
0
AllisonKennedy
Super User
Super User
In response to learner03

‎03-08-2022 01:05 AM

@learner03 You can just add +1 to make it the 1 March, but keep in mind the _PeriodStart variable is giving the end of the month to start the DATESINPERIOD() function, and then count backwards the number of months you put in the _Length variable, so not sure why you'd want it to be 1 March? 28 Feb seems like the correct start date to give past 1 month not counting this month??


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Message 6 of 6
333 Views

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.

View All