Forum Discussion
Post PatronHow 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
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-e3174b39f38aTo 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.
7 Replies
CNENFRNLCommunity Champion
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.
NilRThank you! However my data is YearMonth as Text, what do you recommend?
- PhilipTreacy
Super User
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. - amitchandak
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-e3174b39f38aTo 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.- NilRI appreciate you taking the time to answer my question!!!
- NilRThank you! However my data is YearMonth as Text, what do you recommend?
- PhilipTreacy
