cancel
Showing results for 
Search instead for 
Did you mean: 
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

3 REPLIES 3
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/

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/

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.