CODEWITHSUNDEEP
powerbidax
user9832031
user9832031

Reputation: 39

DATEADD to calculate PYTD (Jan 1 till today's date) in DAX Power BI

I have a daily sales data for 3-4 years. I want to create the YTD and Prior Year To Date sales measure that will be updated daily. That is it should always be from beginning of the year (selected) to TODAY or the last day of the data (1 day lag from today and max date).

I used Sameperiodlastyear but it is problematic at the beginning of the month as it compares say Jan 1, 2022-June 8 2022 with Jan 1, 2021 with June 30, 2021.

Any suggestion how I can create a modified prior year to date measure to address this nuance?

Upvotes: 0

Views: 2086

Answers (1)

Mik
Mik

Reputation: 2103

This is a standard solution for the case. First, you get all dates, with a DATESYTD() function, for the current year or last visible year up today or last visible day, then you offset it.

SAMEPERIODLASTYEAR(DATESYTD(‘Date’[Date]))

It is equal to

DATEADD(DATESYTD(‘Date’[Date]),-1,YEAR))

Try this if you want to get exact days set:

VAR FirstDayThisYear = 
        SAMEPERIODLASTYEAR(STARTOFYEAR(‘Date’[Date])
VAR LastDayThisYear = 
        SAMEPERIODLASTYEAR(
                          LASTDATE(‘Date’[Date])
        )
VAR SetOfDates= 
        DATESBETWEEN(
                    ‘Date’[Date]
                    ,FirstDayThisYear 
                    ,LastDayThisYear 
        )
RETURN 
    SetOfDates

Upvotes: 0

Related Questions