How can I change Date.ToText to previous Year

[NilR]

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

[amitchandak · Accepted solution]

@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.

[NilR]

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

[PhilipTreacy]

Hi @NilR 

Is that question for me?

Phil

[NilR]

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

[PhilipTreacy]

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.

[CNENFRNL]

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.

[NilR]

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