Calculate Sales for Last Year Month to date

Sameperiodlastyear till to date based on selection in year and month selection

Problem: 

• If the current year and month are selected, sales data from the same period of the previous year up to the current date is needed.
• For instance, if it's March 2024, you'd require sales data from March 2023 up to today's date.
• If the selection is for a different year and month, such as January 2023, you need the entire sales data for January 2022.

Solution:

 For this scenario you need to create Three Dax Measures

Measure 1 = (This measure is to calculate current month sales till the date)

CALCULATE( SUM(CST[Total Financed Amount]), SAMEPERIODLASTYEAR( DATESYTD('Calendar Table'[Date]) ), FILTER( ALL('Calendar Table'), 'Calendar Table'[MonthNum] = MONTH(NOW()) && DAY('Calendar Table'[Date]) <= DAY(NOW()) ) )

Explanation:

CALCULATE: This is a DAX function used to modify or filter the context for a calculation. It can apply additional filters to a calculation.

SUM(CST[Total Financed Amount]): This part calculates the sum of the "Total Financed Amount" column from a table named "CST".

SAMEPERIODLASTYEAR: This function returns a set of dates that corresponds to the same period in the previous year. In this case, it's returning the dates year-to-date (YTD) from the previous year.

DATESYTD('Calendar Table'[Date]): This function returns a set of dates from the start of the year up to the specified date. It appears to be returning dates from the 'Calendar Table' up to the current date.

FILTER(ALL('Calendar Table'), 'Calendar Table'[MonthNum] = MONTH(NOW()) && DAY('Calendar Table'[Date]) <= DAY(NOW())): This part filters the 'Calendar Table' to include only the dates from the current month up to the current day. It uses the MONTH() and DAY() functions with the NOW() function to get the current month and day. In summary, this measure calculates the sum of the "Total Financed Amount" for the same period last year (up to the current date) if the current month and day are selected. It's effectively providing a comparison of year-to-date sales between the current year and the previous year, up to the same date.

Measure 2 = (This measure is to calculate Same period last year)

CALCULATE( SUM(CST[Total Financed Amount]), SAMEPERIODLASTYEAR( DATESYTD('Calendar Table'[Date]) ), FILTER( ALL('Calendar Table'), 'Calendar Table'[MonthNum] <= (MONTH(NOW())-1) ) )+ CALCULATE( SUM(CST[Total Financed Amount]), SAMEPERIODLASTYEAR( DATESYTD('Calendar Table'[Date]) ), FILTER( ALL('Calendar Table'), 'Calendar Table'[MonthNum] = MONTH(NOW()) && DAY('Calendar Table'[Date]) <= DAY(NOW()) ) )

Explanation:

First Part:

CALCULATE( SUM(CST[Total Financed Amount]), SAMEPERIODLASTYEAR( DATESYTD('Calendar Table'[Date]) ), FILTER( ALL('Calendar Table'), 'Calendar Table'[MonthNum] <= (MONTH(NOW())-1) ) )

SUM(CST[Total Financed Amount]): Calculates the sum of the "Total Financed Amount" column from a table named "CST."

SAMEPERIODLASTYEAR(DATESYTD('Calendar Table'[Date])): Retrieves the set of dates representing the same year-to-date period as the current year, but from the previous year.

FILTER(ALL('Calendar Table'), 'Calendar Table'[MonthNum] <= (MONTH(NOW())-1)): Filters the 'Calendar Table' to include only the dates from previous months (up to the month before the current month). This is done by comparing the month numbers in the 'Calendar Table' to the month number of the current date minus 1.

Second Part:

CALCULATE( SUM(CST[Total Financed Amount]), SAMEPERIODLASTYEAR( DATESYTD('Calendar Table'[Date]) ), FILTER( ALL('Calendar Table'), 'Calendar Table'[MonthNum] = MONTH(NOW()) && DAY('Calendar Table'[Date]) <= DAY(NOW()) ) )

SUM(CST[Total Financed Amount]): Same as in the first part, calculates the sum of the "Total Financed Amount" column from the "CST" table.

SAMEPERIODLASTYEAR(DATESYTD('Calendar Table'[Date])): Same as in the first part, retrieves the set of dates representing the same year-to-date period as the current year, but from the previous year.

FILTER(ALL('Calendar Table'), 'Calendar Table'[MonthNum] = MONTH(NOW()) && DAY('Calendar Table'[Date]) <= DAY(NOW())): Filters the 'Calendar Table' to include only the dates from the current month up to the current day. This is done by comparing the month number and the day of the 'Calendar Table' dates to the current month and day.

Explanation:

In essence, this measure is calculating the year-to-date sum of the "Total Financed Amount" for both the previous year (up to the month before the current month) and the current year (up to the current day). It's designed to compare the year-to-date financial performance for the same period in the current and previous years. This could be useful for analyzing trends and variations in financial data over time.

Measure 3 = (Switch between the Measure 1 & Measure 2 Based on Year and Month Selections)

Fin Amount MTD = IF ( SELECTEDVALUE('Calendar Table'[MonthNum]) = MONTH(TODAY()), [Measure1] ,[Measure2]) Measure 4 = CALCULATE([FinAmount], SAMEPERIODLASTYEAR('Calendar Table'[Date])) Measue 4 = (This is the final Measure to use in visual)

IF ( SELECTEDVALUE('Calendar Table'[Year]) = YEAR(TODAY()) ,[Fin Amount MTD] ,[Fin Amt SPLY])

Instead of creating Three Measures you can create one Measure using

VAR Function it will work same as above.

Please leave comments If you have any questions and issues on Power BI DAX I am here to help you.

Happy Learning 😊