date handling for accumulated month, year, previous year using only a reference date

[Anonymous]

What is the best way to determine the value of the day (date informed in the segmenter-with the start and end date being the same date-Example: 02/03/2022-02/03/2022),

accumulated value of the month (date informed in the segmenter-with the start and end date being the same date-Example: 03/02/2022-03/02/2022, in which case

the monthly accumulated must be from the first day of the month until the date informed in the segmenter),

accumulated value of the current year (date informed in the segmenter-with the start and end date being the same date-Example: 02/03/2022-02/03/2022, in which case

the monthly accumulated must be from the first day of the year until the date informed in the segmenter)),

accumulated value of the month of the previous year (date informed in the segmenter-with the start and end date being the same date-Example: 02/03/2022-02/03/2022,

in which case the monthly accrual must be from the first day of the previous month to the last day of the previous year)

[amitchandak · Accepted solution]

@Anonymous , have check datesmtd, datesytd ?

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,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"))

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

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

[Anonymous]

What is the best way to determine the value of the day (date informed in the segmenter-with the start and end date being the same date-Example: 02/03/2022-02/03/2022),

accumulated value of the month (date informed in the segmenter-with the start and end date being the same date-Example: 03/02/2022-03/02/2022, in which case

the monthly accumulated must be from the first day of the month until the date informed in the segmenter),

accumulated value of the current year (date informed in the segmenter-with the start and end date being the same date-Example: 02/03/2022-02/03/2022, in which case

the annual accrual must be from the first day of the year to the date informed in the segmenter)),

accumulated value of the month of the previous year (date informed in the segmenter-with the start and end date being the same date-Example: 02/03/2022-02/03/2022,

in which case the annual accrual must be from the first day of the previous month to the last day of the previous year)

[Anonymous]

I already have a calendar table created by the CALENDARAUTO() formula

[amitchandak · Accepted solution]

@Anonymous , have check datesmtd, datesytd ?

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,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"))

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

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

[Anonymous · Accepted solution]

Thank you amitchandak. In the case of the monthly one in which the user will only inform a date, I was not able to filter using filter, format in the date field for year and month together. In the case of the solution https://amitchandak.medium.com/power-bi-mtd-questions -time-intelligence-3-5-64b0b4a4090e

Due to the dates being very dynamic, it gets complicated. I thought of a solution that could filter for example by a date field and that the month and year of the table field were equal to the month and year of the dates informed in the data slicer, but unfortunately I couldn't.