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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
vpatel55
Kudo Commander
Kudo Commander

Shifting dates one year back

I am trying to shift a column of dates by one year.  I have tried SAMEPERIODLASTYEAR, PARALLELLPERIOD and DATEADD. I want to avoid simply subtracting 365 days.

 

They all return the error "The True / False express does not specify a column. Each True / False expressions used as a table filter must refer to exactly the one column.

 

However, I have used SELECTCOLUMNS to isolate the one column.

 

This is intended to work as follows. The start date and yesterdays date of the current year are stored in variables StartDate and YesterdayDate. Next, a calculated table with these dates are stored in MtdTable. I then use SELECTCOLUMNS to isolate just the date column in the calculated table. This is then used within PARALELLPERIOD.

 

Right answer gets a Kudo 🙂

 

 

Expression =
VAR ThisMonthStartDate =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
VAR StartDate =
    IF ( DAY ( TODAY () ) > 1, ThisMonthStartDate, EOMONTH ( TODAY (), -2 ) + 1 )
VAR YesterdayDate =
    TODAY () - 1
VAR MtdTable =
    FILTER (
        ALL ( dimCalendar ),
        dimCalendar[Date] <= YesterdayDate
            && dimCalendar[Date] >= StartDate
    )
VAR SelectDateColumnOnly =
    SELECTCOLUMNS ( MtdTable, "Date", VALUES ( dimCalendar[Date] ) )
VAR ShiftToLastYear =
    CALCULATE ( PARALLELPERIOD ( SelectDateColumnOnly, -1, YEAR ) )
VAR _Calculation =
    CALCULATE ( [Expression], ShiftToLastYear )
RETURN
    _Calculation

 

 

1 ACCEPTED SOLUTION

@amitchandak pointed me in the right direction such that I could use the inbuilt time shifting functions but ignoring the current day. I'm including this post too if anyone finds it useful, as I wanted MTD last year, but ingoring the current day. Here is the code for that:

 

 

MTD previous year = 
VAR _max =
    TODAY () - 1
VAR _min =
    EOMONTH ( _max, -1 ) + 1
RETURN
    CALCULATE (
        [Expression)],
        DATEADD ( DATESBETWEEN ( dimCalendar[Date], _min, _max ), -1, YEAR )
    )

 

 

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@vpatel55 , I doubt correct usage. Always use date tbale

refer examples

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 year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

 

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

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

Hi @amitchandak I'm afraid I can't easily use the inbuilt MTD functions because our data is one day behind. So on the 1st day of the month, it needs to refer to the whole of the previous month. Only on the 2nd of the month, should it start reporting for the current month. So I need to shift my StartDate and YesterdayDate dates. Any ideas?

@vpatel55 , try this

 

MTD a day before =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())-1
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

 

or

 

MTD a day before =
var _max = if(isfiltered('Date'),MAXX(allselected('Date'), 'Date'[Date]) , today())-1
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

 

You can also check

https://medium.com/chandakamit/cheat-sheet-power-bi-time-intelligence-formulas-using-today-654f26e27...

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

Hi @amitchandak thanks for your response, I think we are nearly there, The top formula returned back some of today's numbers, which I wanted to avoid. However, removing the ISFILTERED and MAX date solves that, and brings the correct answer:

 

Expression = 
var _max = today()-1
var _min = eomonth(_max,-1)+1
return
CALCULATE([Measure] ,DATESBETWEEN(dimCalendar[Date],_min,_max))

 Can you confirm that I have not ruined this so that it will still show the complete previous month on the 1st of the month, and it will still not begin counting this months numbers until the 2nd of the month (but always excluding the current day)?

 

Thanks again.

@amitchandak pointed me in the right direction such that I could use the inbuilt time shifting functions but ignoring the current day. I'm including this post too if anyone finds it useful, as I wanted MTD last year, but ingoring the current day. Here is the code for that:

 

 

MTD previous year = 
VAR _max =
    TODAY () - 1
VAR _min =
    EOMONTH ( _max, -1 ) + 1
RETURN
    CALCULATE (
        [Expression)],
        DATEADD ( DATESBETWEEN ( dimCalendar[Date], _min, _max ), -1, YEAR )
    )

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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