cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors