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

Shape 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.

Reply
ChumaAmako
Helper I
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
 
Thanks for your help 🙂
 
 
 
1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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/

View solution in original post

11 REPLIES 11
007Raj
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

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


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

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

DateTotal SalesMTD_LY Sales
01-Mar-248002967028
02-Mar-24116384145154
03-Mar-2475338227393
04-Mar-2477774326290
05-Mar-2454493389099
06-Mar-2467590467828
07-Mar-2497445570431



Outpout needed (without date column and date picker inside the report)

LOCATIONMTD_LY SalesMTD_LY_Sales_DSR
Store110141013.5
Store2117686117686
Store395889588.35
Store481068106
Store52416424164
Store62447724476.851
Store710561056
Store8161160.65

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
https://www.linkedin.com/in/excelenthusiasts/

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.


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

@ChumaAmako 

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/

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
v-gizhi-msft
Community Support
Community Support

Hi,

 

Please take following steps:

1)Delete the relationship between them:

1.PNG

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:

2.PNG

Here is the test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto Zhi

 

Ashish_Mathur
Super User
Super User

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/

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.


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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.