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.
Hi
I've a transactional table that contains the sales done for each day.
What I'm trying to achieve is to compare sales by each sales person as follows:
1) YTD sales this year VS last year. So, assume we are now in Oct. So, I want to compare YTD Sales till Oct 2021 compared to YTD sales last year (Jan till Oct 2020).
2) Monthly sales this year compared to last year. E.g. Jan 2020 VS Jan 2021, Feb 2020 VS Feb 2021
3) I want it to be dynamic, so let's stay we start 2022. Then, my current fiscal year will be 2022 and I want to compare in this case YTD Jan 2022 to Jan 2021.
Sampe data below:
Date | Sales Person | Item Number | Amount ($) | QTY |
01-Jan-20 | Sales A | 1001 | $ 200.00 | 3 |
01-Jan-20 | Sales B | 1002 | $ 100.00 | 4 |
02-Feb-20 | Sales C | 1003 | $ 300.00 | 5 |
02-Feb-20 | Sales A | 1004 | $ 500.00 | 4 |
03-Mar-20 | Sales B | 1005 | $ 1,000.00 | 7 |
03-Mar-20 | Sales C | 2001 | $ 900.00 | 8 |
04-Apr-20 | Sales A | 2002 | $ 950.00 | 2 |
04-Apr-20 | Sales B | 2003 | $ 302.00 | 4 |
01-May-20 | Sales C | 2004 | $ 311.00 | 1 |
01-May-20 | Sales A | 2005 | $ 400.00 | 9 |
02-Jun-20 | Sales B | 3001 | $ 100.00 | 5 |
02-Jun-20 | Sales C | 3002 | $ 200.00 | 6 |
03-Jul-20 | Sales A | 3003 | $ 300.00 | 3 |
03-Jul-20 | Sales B | 3004 | $ 800.00 | 4 |
04-Aug-20 | Sales C | 3005 | $ 900.00 | 5 |
04-Aug-20 | Sales A | 4001 | $ 400.00 | 8 |
06-Sep-20 | Sales B | 4002 | $ 500.00 | 2 |
06-Sep-20 | Sales C | 4003 | $ 600.00 | 4 |
01-Oct-20 | Sales A | 4004 | $ 300.00 | 8 |
01-Oct-20 | Sales B | 4005 | $ 400.00 | 9 |
02-Nov-20 | Sales C | 5001 | $ 100.00 | 4 |
02-Nov-20 | Sales A | 5002 | $ 950.00 | 2 |
03-Dec-20 | Sales B | 5003 | $ 750.00 | 6 |
03-Dec-20 | Sales C | 5004 | $ 650.00 | 5 |
01-Jan-21 | Sales C | 1001 | $ 300.00 | 1 |
02-Feb-21 | Sales A | 1002 | $ 350.00 | 1 |
03-Mar-21 | Sales B | 1003 | $ 450.00 | 3 |
04-Apr-21 | Sales C | 1004 | $ 650.00 | 2 |
01-May-21 | Sales A | 1005 | $ 300.00 | 4 |
02-Jun-21 | Sales B | 2001 | $ 700.00 | 5 |
03-Jul-21 | Sales C | 2002 | $ 850.00 | 6 |
04-Aug-21 | Sales A | 2003 | $ 830.00 | 7 |
06-Sep-21 | Sales B | 2004 | $ 100.00 | 9 |
01-Oct-21 | Sales C | 2005 | $ 150.00 | 2 |
Thanks in Advance
Solved! Go to Solution.
Hi @Anonymous
You can achieve this by creating the following measures:
1. SalesMTD
SalesMTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATESMTD ( 'Date'[Date] ) )
2. SalesYTD
SalesYTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATESYTD ( 'Date'[Date] ) )
3. SalesLstYrMTD
SalesLstYrMTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATEADD ( 'Date'[Date] , -1 , MONTH ) )
4. SalesLstYTD
SalesLstYTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATEADD ( 'Date'[Date] , -1 , YEAR ) )
If you haven't already created a Date table, I strongly recommend you create one. Link the 'Date'[Date] field to your 'Table'[Date] field that will automatically generate a one to many relationship.
Date =
VAR MinYear = YEAR ( MIN ( 'Table'[Date] ) )
VAR MaxYear = YEAR ( MAX ( 'Table'[Date] ) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Calendar Year", "CY " & YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] )
)
Let me know if you need further assistance!
Hope this helps 🙂
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Hi @Anonymous
You can achieve this by creating the following measures:
1. SalesMTD
SalesMTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATESMTD ( 'Date'[Date] ) )
2. SalesYTD
SalesYTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATESYTD ( 'Date'[Date] ) )
3. SalesLstYrMTD
SalesLstYrMTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATEADD ( 'Date'[Date] , -1 , MONTH ) )
4. SalesLstYTD
SalesLstYTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATEADD ( 'Date'[Date] , -1 , YEAR ) )
If you haven't already created a Date table, I strongly recommend you create one. Link the 'Date'[Date] field to your 'Table'[Date] field that will automatically generate a one to many relationship.
Date =
VAR MinYear = YEAR ( MIN ( 'Table'[Date] ) )
VAR MaxYear = YEAR ( MAX ( 'Table'[Date] ) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Calendar Year", "CY " & YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] )
)
Let me know if you need further assistance!
Hope this helps 🙂
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Thanks a lot @TheoC
Qustion. The fiscal year for me starts on 1st Oct and Ends in 30 Sep. How can I use the above in this case? Is there a way to setup start of fiscal year?
@Anonymous absolutely. You can adjust the SalesYTD to the following:
SalesYTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATESYTD ( 'Date'[Date] , "09-30" ) )
and replicate the same for the others 🙂
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
BTW @TheoC , does the above work with DATESMTD ? It gives me syntax error.
Also, how can I use this for the following DAX
SalesLstYrMTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATEADD ( 'Date'[Date] , -1 , MONTH ) )
You are Awesome @TheoC
Just I noticed the following on your solution:
The following formula is not working, it doesn't accept to add the end date.
SalesMTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATESMTD ( 'Date'[Date] , "09-30" ) )
How can I incorporate Fiscal Year end in this formula?
SalesLstYrMTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATEADD ( 'Date'[Date] , -1 , MONTH ) )
Thanks
Hi @Anonymous
A pleasure my friend.
Okay, for the SalesMTD, you won't need to worry about the Fiscal Year End because it only sums to the MTD. The same will be applicable for the SalesLstYrMTD so you don't need to worry about the year end period.
Let me know if that makes sense?
Thanks heaps,
Theo
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Hi @TheoC it makes sense but not working for some reason 😞
Here is what I'm facing.
For this measure:
SalesYTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATESYTD ( 'Date'[Date] , "09-30" ) )
If I use the date table that I created per your advise in the above measure, I get blank when I use the "09-30". BUT if I use the date that is in the original table "Table[Date]", then I get results. But I need to define the end of fiscal year for my results to be right.
So, if I can solve this, then I think all will work fine.
@Anonymous Apologies, can you chant "09-30" to "9/30" apologies!
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Nop, not solving the problem.
So here's the behaviour.
1) When I put this measure for YTD in this format:
SalesYTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATESYTD ( 'Date'[Date] , "09-30" ) )
I get 0 as a result.
2) When I put this measure for YTD in this format (I use date in transaction table):
SalesYTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATESYTD ( 'Table'[Date] , "09-30" ) )
I get results correct.
Now for last year YTD:
When I use this measure
SalesLstYrMTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATEADD ( 'Date'[Date] , -1 , MONTH ) )
I get values, but the sum is wrong. I think it's unaware of the Start and end of quarter. This is why the result is wrong.
Just sharing my thoughts.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |