Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hi All,
I need to create a measure to calculate the MTD Sales for the same period last year based on the latest daily sales report saved in the sales table.
I have 2 tables, one a daily sales table and a calendar table for this. Please find HERE a sample PowerBI file if needed
I have written the measure:
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Thanks for quick response
What I was looking for is
Without any date selection via filter in the report
want MTD Last Year sales, i.e., today I have sales of current month from 1st Mar to 6th Mar 2024 in Current Year MTD sales but no date is selected (below dax).
Now I want MTD Last Year sales for same dates 1st Mar to 6th Mar 2023 ( below dax
Sharing a measure will not help. Share some data to work with and show the expected result.
Hi Ashish
For instance, I have sales and last year sales in front of dates like below and I want to calculate same period last year sales without date selection for stores/brands, etc without date column and date picker inside the report....
Date | Total Sales | MTD_LY Sales |
01-Mar-24 | 80029 | 67028 |
02-Mar-24 | 116384 | 145154 |
03-Mar-24 | 75338 | 227393 |
04-Mar-24 | 77774 | 326290 |
05-Mar-24 | 54493 | 389099 |
06-Mar-24 | 67590 | 467828 |
07-Mar-24 | 97445 | 570431 |
Outpout needed (without date column and date picker inside the report)
LOCATION | MTD_LY Sales | MTD_LY_Sales_DSR |
Store1 | 1014 | 1013.5 |
Store2 | 117686 | 117686 |
Store3 | 9588 | 9588.35 |
Store4 | 8106 | 8106 |
Store5 | 24164 | 24164 |
Store6 | 24477 | 24476.851 |
Store7 | 1056 | 1056 |
Store8 | 161 | 160.65 |
Hi,
I cannot understand your question at all. Without selecting a date/month, how does one calculate MTD?
as of today automatically it should calculate - MTD, i.e., if its 9th Mar 2024, (TODAY(), then measure automactically calculate MTD sales, i.e., 1st to 9th Mar, 2024 (Current year) and 1st Mar to 9th Mar 2023 (Previous year)
similarly for YTD also I need same calculation.
Hope its clear now.
Hi,
Share the download link of the PBI file.
For time intelligence-related stuff you should use a date calendar, you date from the calendar
Example
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
last QTR same Month (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))
MTD (Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Hi,
Please take following steps:
1)Delete the relationship between them:
2)Create a calculated column in 'Daily Sales' table:
Year&Month = FORMAT('Daily Sales'[Date],"YYYY-MMM")
3)Try this measure:
Measure =
VAR a =
SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
SUMX (
GROUPBY ( 'Daily Sales', 'Daily Sales'[Date], 'Daily Sales'[Year&Month] ),
CALCULATE (
IF (
MAX ( 'Daily Sales'[Date] )
>= DATE ( YEAR ( a ) - 1, MONTH ( a ), 1 )
&& MAX ( 'Daily Sales'[Date] )
<= DATE ( YEAR ( a ) - 1, MONTH ( a ), DAY ( a ) ),
CALCULATE (
SUM ( 'Daily Sales'[Sales] ),
FILTER (
'Daily Sales',
[Date] <= MAX ( 'Daily Sales'[Date] )
&& [Year&Month] IN FILTERS ( 'Daily Sales'[Year&Month] )
)
),
0
)
)
)
4)Choose this measure as a card visual, 'Calendar'[Date] as a slicer.
When select March 24 in slicer, the result shows:
Here is the test pbix file:
Hope this helps.
Best Regards,
Giotto Zhi
Hi,
You may download my PBI file from here.
Hope this helps.
I am not unable see the PBIX file in the link
I do not have the file. Describe the question, share data in a format that can be pasted in an MS Excel file and show the expected result.
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
91 | |
86 | |
83 | |
76 | |
49 |
User | Count |
---|---|
145 | |
140 | |
109 | |
68 | |
55 |