cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## MTD - Same Period Last Year with Missing Dates

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:

SPLY MTD for LastReportDate = CALCULATE([Total Sales],DATESMTD(DATEADD(LASTDATE('Daily Sales'[Date]),-1,YEAR)),ALLEXCEPT('Daily Sales','Daily Sales'[Date]))

This works beautifully until I have a date (e.g. 24 March 2020) which will fall on a non-working day in the previous year.

The correct outcome from the measure for SPLY MTD as at 24 Mar 2020 should be 73,201

1 ACCEPTED SOLUTION
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
11 REPLIES 11
Frequent Visitor

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

MTD_LY_Sales_DSR) but in this going back 365 days is not correct way to do it.  so needed assistance on getting ly sales without date selection in the report....automatically for the current month it should last year too

MTD_Sales_DSR =

VAR _todaymonthstart =
EOMONTH ( TODAY (), -1 ) + 1
VAR _todaymonthend =
EOMONTH ( TODAY (), 0 )
RETURN
IF (
HASONEVALUE ( DimDate[Monthnumber] ) && HASONEVALUE ( DimDate[Year] ),
TOTALMTD (
[Total Sales],
DimDate[Date]
),
CALCULATE (
[Total Sales],
FILTER (
ALL ( DimDate ),
DimDate[Date] >= _todaymonthstart
&& DimDate[Date] <= _todaymonthend
)
)
)

MTD_LY_Sales_DSR =

VAR _todaymonthstart =
EOMONTH ( TODAY (), -13 ) + 1   -- Go back 13 months to the same month last year
VAR _yesterday =
TODAY () - 365   -- Adjust to the same day last year

VAR _Result =
IF (
HASONEVALUE ( DimDate[Monthnumber] ) && HASONEVALUE ( DimDate[Year] ),
TOTALMTD (
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR(DimDate[Date])  -- Adjust to fetch same period last year
),
DimDate[Date]
),
CALCULATE (
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR(DimDate[Date])  -- Adjust to fetch same period last year
),
FILTER (
ALL ( DimDate ),
DimDate[Date] >= _todaymonthstart
&& DimDate[Date] <= _yesterday
)
)
)

RETURN
_Result
Super User

Sharing a measure will not help.  Share some data to work with and show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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
Super User

Hi,

I cannot understand your question at all.  Without selecting a date/month, how does one calculate MTD?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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.

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Community Support

Hi,

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

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

I am not unable see the PBIX file in the link

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors