YTD with Date selection

[IF]

Hi,

I have two slicer in my app. One of  them is month selection and the other one is for office selection. I would like to get YTD data for each year. I wrote the following measure:

Sales YTD = TOTALYTD(SUM('TotalSales'[Sales]), 'Date'[MonthYearDateFormat])

it brings me YTD data if the date is not selected. However, I want to see YTD data even the date is selected. if I select 12.2019, I want to see YTD data for 2019. Similarly, if I select 01.2020, I want to see data for 2020.

Background information:

I have a table named TotalSales which has 3 columns OfficeCode, Month and Sales. OfficeCode is integer, Month is text and Sales is integer.

OfficeCode Month Sales

1	11.2019	4
1	12.2019	5
1	01.2020	8
1	02.2020	7
1	03.2020	3
2	11.2019	9
2	12.2019	8
2	01.2020	11
2	02.2020	12
2	03.2020	16

Date table has three columns as shown below.

MonthYearTextFormat MonthYearDateFormat Order

11.2019	1/11/2019	1
12.2019	1/12/2019	2
01.2020	1/1/2020	3
02.2020	1/2/2020	4
03.2020	1/3/2020	5

Office table has two columns:

OfficeName OfficeCode

Tanum	1
Eskils	2

How can I see YTD data, even the month is selected? if month is selected "12.2019" or "11.2019", I want to show YTD data for whole 2019. Also if month is selected "01.2020" or "02.2020" or "03.2020", I want to see YTD data for 2020.

Thanks in advance! 🙂

[dax]

Hi  @IF , 

I am not sure whether this is what you want, you could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[IF]

Hi,

It is almost what I want. I want to have one more slicer to select the office. Instead of using table, I want to show the numbers in a card.Is it possible to do in that way?

All the best

[dax · Accepted solution]

Hi @IF ， 

You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@IF , Create a date from month year -formatted -11.2019

Date = date(right([month year],4), left([month year],2),1)

Now you can use datesytd and date calendar. There is the formula to get the complete year.

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))
//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

Check -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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[IF]

Hi,

Thanks for the reply.

I want to have a measure that will give me YTD data based on selection. 

Below MonthYearTextFormat selection can be 2020 or 2019. I want to see YTD data based on the selection. I tried the ones that you provided, but they are not actually in the way that  I am looking for.