cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

How can I change Date.ToText to previous Year

Hi All,

 

here is my Syntax:

 

Table.SelectRows(#"Filtered MBR_MOS", each [YRMO] >= Date.ToText(StartDate,"yyyyMM") and [YRMO] <= Date.ToText(EndDate,"yyyyMM"))

 

 

When a date range is selected, I would like this syntax filter to previous year but same month.

 

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

@NilR , If you are looking for slicer selection, means you are looking for last year measure, for that you need to use TI and date table.

In MT you have function

Date.AddYears([Date],-1)

 

 

For Dax last year data check example

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"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

 

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

 

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 :radacad sqlbi My Video Series Appreciate your Kudos.

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

7 REPLIES 7
Highlighted
Memorable Member
Memorable Member

Hi, @NilR , in order to offset a date one year forward, it's supposed to use,

Date.AddYears(#"Some Date", -1)

apropos, I didn't quite catch your idea of "a date range is selected"; you mean pass some parameters to a query in Power Query? Unlike DAX, there's no slicer-wise selection in Power Query.

Highlighted
Resident Rockstar
Resident Rockstar

Hi @NilR 

Given that StartDate and EndDate are storing dates then this line works to filter for the current month, but previous year

 

 Table.SelectRows(#"Filtered MBR_MOS" , each [YRMO] >= Date.AddYears(StartDate, -1) and [YRMO] <= Date.AddYears(EndDate, -1))

 

Phil 


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

Highlighted
Super User IV
Super User IV

@NilR , If you are looking for slicer selection, means you are looking for last year measure, for that you need to use TI and date table.

In MT you have function

Date.AddYears([Date],-1)

 

 

For Dax last year data check example

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"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

 

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

 

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 :radacad sqlbi My Video Series Appreciate your Kudos.

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted

I appreciate you taking the time to answer my question!!!
Highlighted

Thank you! However my data is YearMonth as Text, what do you recommend?
Highlighted

Thank you! However my data is YearMonth as Text, what do you recommend?
Highlighted

Hi @NilR 

Is that question for me?

Phil

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors